# Thread: sumif overmultiple columns formula (2003)

1. ## 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.

2. ## Re: sumif overmultiple columns formula (2003)

Hans

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

3. ## Re: sumif overmultiple columns formula (2003)

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)

4. ## 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)

5. ## 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.

6. ## 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.

7. ## 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) )

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•