# 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

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

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

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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1