Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 9
There are 1 users currently browsing forums.
|
 |
05-14-2009, 11:02 AM
|
|
Registered User
|
|
Join Date: 14 May 2009
Location: London
MS Office Version:Excel 2003
Posts: 2
|
|
|
Help with sumif()
Please Register to Remove these Ads
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
|
05-14-2009, 11:05 AM
|
 |
Forum Guru
|
|
Join Date: 03 Apr 2007
Location: Skelmersdale UK, probably.
MS Office Version:XL2003 / 2007
Posts: 1,720
|
|
|
Re: Help with sumif()
Hi,
Try
=SUMPRODUCT((B1:B10=1)*(G1:G10))
__________________
This week, I will be mostly overcomplicating things
|
05-14-2009, 11:07 AM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,500
|
|
|
Re: Help with sumif()
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
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
05-14-2009, 11:12 AM
|
|
Registered User
|
|
Join Date: 14 May 2009
Location: London
MS Office Version:Excel 2003
Posts: 2
|
|
|
Re: Help with sumif()
Bingo!
Thanks so much! You've REALLY helped me out! 
I've never used SUMPRODUCT() before either.
Cheers,
Tom
|
05-14-2009, 11:15 AM
|
 |
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
|
|
|
Re: Help with sumif()
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.
Last edited by DonkeyOte; 05-14-2009 at 11:20 AM.
|
05-14-2009, 11:17 AM
|
|
Forum Guru
|
|
Join Date: 03 Apr 2007
Location: Skelmersdale UK, probably.
MS Office Version:XL2003 / 2007
Posts: 1,720
|
|
|
Re: Help with sumif()
Indeed it would.
I appear to be suffering with sumproduct blindness.
|
05-14-2009, 11:18 AM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,500
|
|
|
Re: Help with sumif()
DonkeyOte...
Do you mean: SUMPRODUCTS generally only required if you have 2+ tests?
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
05-14-2009, 11:19 AM
|
|
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
|
|
|
Re: Help with sumif()
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 !
|
05-14-2009, 11:22 AM
|
|
Forum Guru
|
|
Join Date: 03 Apr 2007
Location: Skelmersdale UK, probably.
MS Office Version:XL2003 / 2007
Posts: 1,720
|
|
|
Re: Help with sumif()
It's also recently become my favourite. I used to love OFFSET, but you made me realise it's a naughty function.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
