Help with sumif()

**tomchubb** · 05-14-2009, 12:02 PM

Please can someone help me with the following?
I'm trying to work out the sum of data in column E based when column B of the row = 1 and so on.
I thought I could do this with a lookup or sumif, but I've tried for two days to find something.
The best I could come up with (to show I have at least tried) was to create extra columns outside of the table I'm working with and use if functions to check wether column B = 1 or not then summing up that column, but it's very messy.

eg:
in G2
=IF($B2=1,$E2,"")

Then
=sum(G1:G10)

Can anyone suggest anything better please?
Thanks in advance,

Tom

**sweep** · 05-14-2009, 12:05 PM

Hi,

Try

=SUMPRODUCT((B1:B10=1)*(G1:G10))

**NBVC** · 05-14-2009, 12:07 PM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Moved to Excel Worksheet Functions forum

**tomchubb** · 05-14-2009, 12:12 PM

Bingo!
Thanks so much! You've REALLY helped me out!

I've never used SUMPRODUCT() before either.
Cheers,

Tom

**DonkeyOte** · 05-14-2009, 12:15 PM

I think a standard SUMIF should suffice, no ?

=SUMIF(B1:B10,1,E1:E10)

SUMPRODUCTS generally only required if you have 2+ tests (and run pre XL2007) and/or are obliged to manipulate the datasets without use of helper cells.

**sweep** · 05-14-2009, 12:17 PM

Indeed it would.

I appear to be suffering with sumproduct blindness.

**NBVC** · 05-14-2009, 12:18 PM

DonkeyOte...

Do you mean: SUMPRODUCTS generally only required if you have 2+ tests?

**DonkeyOte** · 05-14-2009, 12:19 PM

sweep, SUMPRODUCT is my favourite function (how sad that I have a favourite!) but I;ve learnt the hard way to use in moderation and only when necessary ;-)

NBVC, yep - I meant 2+ ... to avoid confusion have edited the post ... thanks !