using sumif to sum multiple columns?

[Dave H]

Is there a way to make a sumif sum multiple columns?

For example I could do =sumif(A1:A5,"Z",B1:B5)+sumif(A1:A5,"Z",C1:C5) would work if I wanted to sum columns B and C. This formula gets pretty long if I want to sum columns B through BB. Isn there a better way to achieve this? I only have one column of criteria, but I have multiple columns I would like to sum if the criteria matches.

Thanks for your help.

 

[Brianwarnock]

How about a different approach, sum the rows

If(a1="Z",sum(b1:bb1),0)+If(A2="Z",Sum(B2:BB2),0)+etc

there will be only 5 of these. Even if there were many just use a separate col to sum each row in a cell using copydown to create the formulae after the first and then sum the column.

Brian

 

[Dave H]

Thanks Brian,

I actually have about 500 rows so that gets a bit awkward too. One approach I could use is just to add a sum column in column BC then just do a Sumif to that column.

I think the best answer I have found so far is a sumproduct((B1:BB5)*(A1:A5="Z")) the Z could actually be a cell reference.

I appreciate your assistance.

Dave

 

[Brianwarnock]

Yes, thought of Sumproduct but haven't used it for years and was a bit too tired from a days walking to set up a test last evening. It is the one I would go for as it will be simple to cope with any changes to the spreadsheet size or criteria changes.

Brian