SUMIF variable number of columns with varying rows in Excel

Question

I'm wanting to SUM a number of rows in Excel, based on a value. In the picture below, you can see the table.

I want to put a value in F1, containing the sum of the values for Jan -> (month # specified in C2) where the Status is the value in C1.

If you do

=SUMIF(B5:B11,"open",INDIRECT(CONCATENATE("C5:",ADDRESS(11,2+C2))))

you get 25 (Cell I2) (which is the sum of C5 and C8), whereas what I actually want as a result is 50 (the sum of C5+D5 (open in Jan and Feb), and C8+D8 (open in Jan and Feb) )

Hope that makes some sense ?

I look forward to any comments, suggestions.

Cheers

Nick

alt text http://ntdd2425.fm.netbenefit.co.uk/table.png

Edit - I'm hoping that I'd be able to do this without creating intermediate columns. The inconsistency in Excel is due to the fact that SUM seems to support summing across multiple columns, but SUMIF doesn't. So this does work:

=SUM(INDIRECT(CONCATENATE(ADDRESS(5,3),":",ADDRESS(5,2+$C$2))))

richardtallent richardtallent · Accepted Answer · 2009-09-03T16:17:12

I would suggest using SUM() with an arrays:

  =SUM(C5:G11*(IF(COLUMN(C5:G11)<=($C$2+1),1,0)*IF($c$1=b5:b11,1,0)))

Enter that with Ctrl-Shift-Enter so it goes in as an array formula.

YMMV, but it worked with a quick test over here.

Basically, you're multiplying three arrays together: one with your data, one with a 1 or 0 based on the month match by column, the last with a 1 or 0 based on the status filter. The arrays don't match in size, so they are repeated.

SUMIF variable number of columns with varying rows in Excel

2 Answers