From: sum if sum if on 24 May 2010 13:13 =SUM(IF('Day 1'!\$C\$4:\$C\$37=A22,'Day 1'!\$D\$4:\$M\$37))+SUM(IF('Day 2'!\$C\$4:\$C\$37=A22,'Day 2'!\$D\$4:\$M\$37))+SUM(IF('Day 3'!\$C\$4:\$C\$37=A22,'Day 3'!\$D\$4:\$M\$37))+SUM(IF('Day 4'!\$C\$4:\$C\$37=A22,'Day 4'!\$D\$4:\$M\$37))+SUM(IF('Day 5'!\$C\$4:\$C\$37=A22,'Day 5'!\$D\$4:\$M\$37))+SUM(IF('Day 6'!\$C\$4:\$C\$37=A22,'Day 6'!\$D\$4:\$M\$37))+SUM(IF('Day 7'!\$C\$4:\$C\$37=A22,'Day 7'!\$D\$4:\$M\$37))+SUM(IF('Day 8'!\$C\$4:\$C\$37=A22,'Day 8'!\$D\$4:\$M\$37))+SUM(IF('Day 9'!\$C\$4:\$C\$37=A22,'Day 9'!\$D\$4:\$M\$37))+SUM(IF('Day 10'!\$C\$4:\$C\$37=A22,'Day 10'!\$D\$4:\$M\$37))+SUM(IF('Day 11'!\$C\$4:\$C\$37=A22,'Day 11'!\$D\$4:\$M\$37))+SUM(IF('Day 12'!\$C\$4:\$C\$37=A22,'Day 12'!\$D\$4:\$M\$37))+SUM(IF('Day 13'!\$C\$4:\$C\$37=A22,'Day 13'!\$D\$4:\$M\$37))+SUM(IF('Day 14'!\$C\$4:\$C\$37=A22,'Day 14'!\$D\$4:\$M\$37))+SUM(IF('Day 15'!\$C\$4:\$C\$37=A22,'Day 15'!\$D\$4:\$N\$37))+SUM(IF('Day 16'!\$C\$4:\$C\$37=A22,'Day 16'!\$D\$4:\$M\$37))+SUM(IF('Day 17'!\$C\$4:\$C\$37=A22,'Day 17'!\$D\$4:\$M\$37))+SUM(IF('Day 18'!\$C\$4:\$C\$37=A22,'Day 18'!\$D\$4:\$M\$37))+SUM(IF('Day 19'!\$C\$4:\$C\$37=A22,'Day 19'!\$D\$4:\$M\$37))+SUM(IF('Day 20'!\$C\$4:\$C\$37=A22,'Day 20'!\$D\$4:\$M\$37))+SUM(IF('Day 21'!\$C\$4:\$C\$37=A22,'Day 21'!\$D\$4:\$M\$37))+SUM(IF('Day 22'!\$C\$4:\$C\$37=A22,'Day 22'!\$D\$4:\$M\$37))+SUM(IF('Day 23'!\$C\$4:\$C\$37=A22,'Day 23'!\$D\$4:\$M\$37))+SUM(IF('Day 24'!\$C\$4:\$C\$37=A22,'Day 24'!\$D\$4:\$M\$37))+SUM(IF('Day 25'!\$C\$4:\$C\$37=A22,'Day 25'!\$D\$4:\$M\$37))+SUM(IF('Day 26'!\$C\$4:\$C\$37=A22,'Day 26'!\$D\$4:\$M\$37))+SUM(IF('Day 27'!\$C\$4:\$C\$37=A22,'Day 27'!\$D\$4:\$M\$37))+SUM(IF('Day 28'!\$C\$4:\$C\$37=A22,'Day 28'!\$D\$4:\$M\$37))+SUM(IF('Day 29'!\$C\$4:\$C\$37=A22,'Day 29'!\$D\$4:\$M\$37))+SUM(IF('Day 30'!\$C\$4:\$C\$37=A22,'Day 30'!\$D\$4:\$M\$37))+SUM(IF('Day 31'!\$C\$4:\$C\$37=A22,'Day 31'!\$D\$4:\$M\$37)) From: Teethless mama on 24 May 2010 14:17 What is your question? "sum if" wrote: > =SUM(IF('Day 1'!\$C\$4:\$C\$37=A22,'Day 1'!\$D\$4:\$M\$37))+SUM(IF('Day > 2'!\$C\$4:\$C\$37=A22,'Day 2'!\$D\$4:\$M\$37))+SUM(IF('Day 3'!\$C\$4:\$C\$37=A22,'Day > 3'!\$D\$4:\$M\$37))+SUM(IF('Day 4'!\$C\$4:\$C\$37=A22,'Day > 4'!\$D\$4:\$M\$37))+SUM(IF('Day 5'!\$C\$4:\$C\$37=A22,'Day > 5'!\$D\$4:\$M\$37))+SUM(IF('Day 6'!\$C\$4:\$C\$37=A22,'Day > 6'!\$D\$4:\$M\$37))+SUM(IF('Day 7'!\$C\$4:\$C\$37=A22,'Day > 7'!\$D\$4:\$M\$37))+SUM(IF('Day 8'!\$C\$4:\$C\$37=A22,'Day > 8'!\$D\$4:\$M\$37))+SUM(IF('Day 9'!\$C\$4:\$C\$37=A22,'Day > 9'!\$D\$4:\$M\$37))+SUM(IF('Day 10'!\$C\$4:\$C\$37=A22,'Day > 10'!\$D\$4:\$M\$37))+SUM(IF('Day 11'!\$C\$4:\$C\$37=A22,'Day > 11'!\$D\$4:\$M\$37))+SUM(IF('Day 12'!\$C\$4:\$C\$37=A22,'Day > 12'!\$D\$4:\$M\$37))+SUM(IF('Day 13'!\$C\$4:\$C\$37=A22,'Day > 13'!\$D\$4:\$M\$37))+SUM(IF('Day 14'!\$C\$4:\$C\$37=A22,'Day > 14'!\$D\$4:\$M\$37))+SUM(IF('Day 15'!\$C\$4:\$C\$37=A22,'Day > 15'!\$D\$4:\$N\$37))+SUM(IF('Day 16'!\$C\$4:\$C\$37=A22,'Day > 16'!\$D\$4:\$M\$37))+SUM(IF('Day 17'!\$C\$4:\$C\$37=A22,'Day > 17'!\$D\$4:\$M\$37))+SUM(IF('Day 18'!\$C\$4:\$C\$37=A22,'Day > 18'!\$D\$4:\$M\$37))+SUM(IF('Day 19'!\$C\$4:\$C\$37=A22,'Day > 19'!\$D\$4:\$M\$37))+SUM(IF('Day 20'!\$C\$4:\$C\$37=A22,'Day > 20'!\$D\$4:\$M\$37))+SUM(IF('Day 21'!\$C\$4:\$C\$37=A22,'Day > 21'!\$D\$4:\$M\$37))+SUM(IF('Day 22'!\$C\$4:\$C\$37=A22,'Day > 22'!\$D\$4:\$M\$37))+SUM(IF('Day 23'!\$C\$4:\$C\$37=A22,'Day > 23'!\$D\$4:\$M\$37))+SUM(IF('Day 24'!\$C\$4:\$C\$37=A22,'Day > 24'!\$D\$4:\$M\$37))+SUM(IF('Day 25'!\$C\$4:\$C\$37=A22,'Day > 25'!\$D\$4:\$M\$37))+SUM(IF('Day 26'!\$C\$4:\$C\$37=A22,'Day > 26'!\$D\$4:\$M\$37))+SUM(IF('Day 27'!\$C\$4:\$C\$37=A22,'Day > 27'!\$D\$4:\$M\$37))+SUM(IF('Day 28'!\$C\$4:\$C\$37=A22,'Day > 28'!\$D\$4:\$M\$37))+SUM(IF('Day 29'!\$C\$4:\$C\$37=A22,'Day > 29'!\$D\$4:\$M\$37))+SUM(IF('Day 30'!\$C\$4:\$C\$37=A22,'Day > 30'!\$D\$4:\$M\$37))+SUM(IF('Day 31'!\$C\$4:\$C\$37=A22,'Day 31'!\$D\$4:\$M\$37)) From: mojohn on 27 May 2010 16:11 I am getting the error #value! I am trying do an if formula that if a user choose from a dropdown list example: list: a1 = mary a2 = bobbie a3 = john =sum(if('sheet 2'!a1:A10 =a1, 'sheet 1'!b1:d10)+=sum(if('sheet 3'!a1:A10 =a1, 'sheet 3'!b1:d10) on sheet one it should give totals but I am getting the error #value! -- Mo John "Teethless mama" wrote: > What is your question? > > > "sum if" wrote: > > > =SUM(IF('Day 1'!\$C\$4:\$C\$37=A22,'Day 1'!\$D\$4:\$M\$37))+SUM(IF('Day > > 2'!\$C\$4:\$C\$37=A22,'Day 2'!\$D\$4:\$M\$37))+SUM(IF('Day 3'!\$C\$4:\$C\$37=A22,'Day > > 3'!\$D\$4:\$M\$37))+SUM(IF('Day 4'!\$C\$4:\$C\$37=A22,'Day > > 4'!\$D\$4:\$M\$37))+SUM(IF('Day 5'!\$C\$4:\$C\$37=A22,'Day > > 5'!\$D\$4:\$M\$37))+SUM(IF('Day 6'!\$C\$4:\$C\$37=A22,'Day > > 6'!\$D\$4:\$M\$37))+SUM(IF('Day 7'!\$C\$4:\$C\$37=A22,'Day > > 7'!\$D\$4:\$M\$37))+SUM(IF('Day 8'!\$C\$4:\$C\$37=A22,'Day > > 8'!\$D\$4:\$M\$37))+SUM(IF('Day 9'!\$C\$4:\$C\$37=A22,'Day > > 9'!\$D\$4:\$M\$37))+SUM(IF('Day 10'!\$C\$4:\$C\$37=A22,'Day > > 10'!\$D\$4:\$M\$37))+SUM(IF('Day 11'!\$C\$4:\$C\$37=A22,'Day > > 11'!\$D\$4:\$M\$37))+SUM(IF('Day 12'!\$C\$4:\$C\$37=A22,'Day > > 12'!\$D\$4:\$M\$37))+SUM(IF('Day 13'!\$C\$4:\$C\$37=A22,'Day > > 13'!\$D\$4:\$M\$37))+SUM(IF('Day 14'!\$C\$4:\$C\$37=A22,'Day > > 14'!\$D\$4:\$M\$37))+SUM(IF('Day 15'!\$C\$4:\$C\$37=A22,'Day > > 15'!\$D\$4:\$N\$37))+SUM(IF('Day 16'!\$C\$4:\$C\$37=A22,'Day > > 16'!\$D\$4:\$M\$37))+SUM(IF('Day 17'!\$C\$4:\$C\$37=A22,'Day > > 17'!\$D\$4:\$M\$37))+SUM(IF('Day 18'!\$C\$4:\$C\$37=A22,'Day > > 18'!\$D\$4:\$M\$37))+SUM(IF('Day 19'!\$C\$4:\$C\$37=A22,'Day > > 19'!\$D\$4:\$M\$37))+SUM(IF('Day 20'!\$C\$4:\$C\$37=A22,'Day > > 20'!\$D\$4:\$M\$37))+SUM(IF('Day 21'!\$C\$4:\$C\$37=A22,'Day > > 21'!\$D\$4:\$M\$37))+SUM(IF('Day 22'!\$C\$4:\$C\$37=A22,'Day > > 22'!\$D\$4:\$M\$37))+SUM(IF('Day 23'!\$C\$4:\$C\$37=A22,'Day > > 23'!\$D\$4:\$M\$37))+SUM(IF('Day 24'!\$C\$4:\$C\$37=A22,'Day > > 24'!\$D\$4:\$M\$37))+SUM(IF('Day 25'!\$C\$4:\$C\$37=A22,'Day > > 25'!\$D\$4:\$M\$37))+SUM(IF('Day 26'!\$C\$4:\$C\$37=A22,'Day > > 26'!\$D\$4:\$M\$37))+SUM(IF('Day 27'!\$C\$4:\$C\$37=A22,'Day > > 27'!\$D\$4:\$M\$37))+SUM(IF('Day 28'!\$C\$4:\$C\$37=A22,'Day > > 28'!\$D\$4:\$M\$37))+SUM(IF('Day 29'!\$C\$4:\$C\$37=A22,'Day > > 29'!\$D\$4:\$M\$37))+SUM(IF('Day 30'!\$C\$4:\$C\$37=A22,'Day > > 30'!\$D\$4:\$M\$37))+SUM(IF('Day 31'!\$C\$4:\$C\$37=A22,'Day 31'!\$D\$4:\$M\$37))  |  Pages: 1 Prev: Compare dates in two different rangesNext: stike through font