# SUMIF across several columns

1. ## SUMIF across several columns

The attached work book shows columns of numbers which can be either from "a" or "b". I use a SUMIF formula for each column to add the figures for "a" and then sum these to get the total number for the whole table
It occurs to me that by using a formula like SUMIF(\$E:\$E,"a",\$F:\$L) I could do this in one go. Unfortunately this formula does not work, so I would be very grateful for one that does.
John  Register To Reply

2. ## Re: SUMIF across several columns

Hi,
How about using this array formula?"

=SUM(IF(\$E\$8:\$E\$15="a",(F8:L15)))

**Array formula should be conformed with CTRL+SHIFT+ENTER

OR using sumproduct:
=SUMPRODUCT((\$E\$8:\$E\$15="A")*(\$F\$8:\$L\$15))  Register To Reply

3. ## Re: SUMIF across several columns

belinda200,
Thank you very much for your interest and help. I couldn't get the sumproduct to work when transferred to my real data, but the array formula worked very well .
The time taken to calculate is very noticeable as compared to my original method. There are 50+ columns and about 3000 rows in the set of which about 15% are selected.
John  Register To Reply

4. ## Re: SUMIF across several columns

The sumproduct should be easier to calculate. why cant you apply that on your original file?

I think it is worth the time and effort checking why it doesn't work for you, and fix that, so that your file will not crash every time you open it....  Register To Reply

5. ## Re: SUMIF across several columns

I agree Belinda, or perhaps creating totals in the next blank column and using a simple sumif

It is also quicker if the ranges are their actual length, selecting a full column has an adverse effect on the calculation speed, as it is looking at redundant cells  Register To Reply

6. ## Re: SUMIF across several columns

I edited my above post....
Can you upload a sample of your original file? I will see why the sumproduct isn't working well.....  Register To Reply