Results 1 to 7 of 7

20040730, 12:33 #1
 Join Date
 Aug 2002
 Location
 milton keynes, Buckinghamshire
 Posts
 252
 Thanks
 0
 Thanked 0 Times in 0 Posts
sumif overmultiple columns formula (2003)
Is there any way to create a simple formula to sumif over multiple columns as follows:
=SUMIF('5.5'!S:S,E3,'5.5'!$D:$M)
The above formula sums over column D only subject to the criteria in E3. I need to sum over columns D to M, where M is the current month and needs to be updated each month so as to get the year to date figure.

20040730, 13:05 #2
 Join Date
 Aug 2002
 Location
 milton keynes, Buckinghamshire
 Posts
 252
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: sumif overmultiple columns formula (2003)
Hans
Can you tell me what is the extra ,0 near the end of the formula is for?

20040730, 13:08 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: sumif overmultiple columns formula (2003)
I used an IF nested in the SUM. The IF function has three arguments: condition, resultifTrue and resultifFalse.
IF('5:5'!S1:S65000=E3,'5.5'!D1:M65000,0)
'5:5'!S1:S65000=E3 is the condition
'5.5'!D1:M65000 is the resultifTrue (these are the data to be added if the condition is true)
0 is the resultifFalse (don't add anything if the condition is false)

20040730, 13:08 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: sumif overmultiple columns formula (2003)
Try the following:
=SUM(IF('5:5'!S1:S65000=E3,'5.5'!D1:M65000,0))
as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. (Array formulas don't work wll with entire columns, so I used rows 1:65000)

20040730, 13:32 #5
 Join Date
 Aug 2002
 Location
 milton keynes, Buckinghamshire
 Posts
 252
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: sumif overmultiple columns formula (2003)
Hans
I have tried the formula. Am I right in thinking that the array multiplication fails if one of the arrays has a cell in it containing text.
In the end I created a sum formula in a spare column in sheet 5.5 and then used sumif('5.5'!S:S,E3,'5.5'!R:R) which seemed to eliminate this problem.

20040730, 13:42 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: sumif overmultiple columns formula (2003)
The array formula should still work if some of the cells contain text (there is no array multiplication involved, by the way.) But your solution is easy and will work well, so I'd use that.

20040730, 16:34 #7
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: sumif overmultiple columns formula (2003)
A non array formula, but still no whole column references,
=SUMPRODUCT(('5.5'!S1:S65000=E3)*('5.5'!D1:M65000) )