sumif overmultiple columns formula (2003)

[pergunas]

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.

[pergunas]

Hans

Can you tell me what is the extra ,0 near the end of the formula is for?

[HansV]

I used an IF nested in the SUM. The IF function has three arguments: condition, result-if-True and result-if-False.

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 result-if-True (these are the data to be added if the condition is true)
0 is the result-if-False (don't add anything if the condition is false)

[HansV]

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)

[pergunas]

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.

[HansV]

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.

[maxflia10]

A non array formula, but still no whole column references,

=SUMPRODUCT(('5.5'!S1:S65000=E3)*('5.5'!D1:M65000) )