SUMIF - I am stuck!
Hello,
I am a novice with Excel and am in need of some help please.
I have 3 columns
A1 | B1 | C1
gMac | 1000 | Ext
dave | 500 | NB
gMac | 500 | NB
gMac | 1000 | EXT
dave | 500 | NB
I would like to get a sum of B1 for 'gMac' if C1 = Ext. This value from the example above would be 2000.
How do I write that in a formula?
=sumifs(b:b,a:a,"gmac",c:c,"ext")
If I've been of help, please hit the star
Legend thank you for the quick reply!!
Do you know if there is a way to do this in Google Docs?
Sorry mate, never used it. My work computer wont let me try it either
I'm sure there will be something very similar if not SUMIFS.
You could also do the same thing using =SUMPRODUCT((A:A="gmac")*(C:C="ext"),B:B)
That might work in Google Docs if the SUMIFS version doesn't.
Although you may have to limit the ranges rather than refer to whole columns. For example:
=SUMPRODUCT((A1:A100="gmac")*(C1:C100="ext"),B1:B100)
That all works in Excel but Google Docs is a nightmare! It is showing error: Circular Dependency Detected - any ideas?
This guys summarises it well but still getting the circular depenency...
What cell are you entering the formula into?
And which cells are you data ranges in?
If you're entering the formula in one of the cells mentioned in the formula itself you will get this circular reference.
So alter the ranges to account for that. So if your data was in rows 1 to 20 and you're entering the formula in row 21 then change all the 100's in the SUMPRODUCT formula to 20.
Does that make sense?
A= Name (gMac)
O= Values (£)
R= Ext or NB
=sum(filter(O4:O20,A4:A20= "gMac";R4:R20="EXT"))
I am entering the above formula into a cell on the same sheet below everything
=sum(filter( ???
Is this a Google Docs thing?
Sorry i though i had pasted the link it - i found a website that offered GoogleDocs specific help on this. http://tech.anoj.net/2011/12/google-...to-sumifs.html
Still no luck! :-(
From the formula you provided I cannot see why you'd be getting this circular reference.
As I said, I'm at work at the moment and the computers here wont let me try Google Docs.
Happy to give it a try when I get home this evening though.
Great, I appreciate the help.
Hello,Originally Posted by Spencer101
Did you get a chance to have a look over the weekend?
works fine in Google docs as long as it's not in one of the referenced cells-which exact cell did you put it in?
Josie
if at first you don't succeed try doing it the way your wife told you to
Hi,
I did, eventually. I sent you a message about it.
I couldn't get it to work at first, but got a different error message to the one you got. I tried the formula again and it worked perfectly.
Not really sure what could be wrong with yours as that's the first (and more than likely last) time I delve into Google Docs. In order to even try and work it out, I'd need to see your spreadsheet.
S.
Hi Spencer101 - i didngt get your email? Where did you send to?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks