I have this data
A B
cat 3
dog 4
mouse 3
fish 12
dog 4
cat 3
fish 12
turnip 27
centipede 100
centipede 100
Ihave named the text list "animals" and the number list "legs"
This formula works: sumif(animals,"=dog",legs) it returns 8
If I put the word dog in cell G5: Can I refer to it in the formula (e.g. =sumif(animals,"=$g$5",legs) --- that doesn't work but can I change the syntax to make it work?
Thanks
Sorry about the formatting but I think you can tell what I meant!
Simply use
=sumif(animals,G5,legs)
without the "=" and quotes
Life's a spreadsheet, Excel!
Say thanks, Click *
=SUMIF(animals,D4,legs)
If it is just "equals" you don't actually need the equals sign.
=SUMIF(animals,"dog",legs is the same as =SUMIF(animals,"=dog",legs)
You could write it as =SUMIF(animals,"="&D4,legs) but there's no advantage.
You would use it, for example, if you wanted a not equal condition: =SUMIF(animals,"<>"&D4,legs)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks all
Can I complicate it slightly
Suppose I wanted a wildcard in the reference cell e.g. not dog but effectively do*, is there a way to do that?
With the criterion in the formula you can use
=SUMIF(animals,"do*",legs)
or if "do" (without quotes) is in cell G5 that can be
=SUMIF(animals,G5&"*",legs)
...or with "do*" in G5 you can just use
=SUMIF(animals,G5,legs)
Audere est facere
=SUMIF(animals,"=do*",legs)
=SUMIF(animals,"do*",legs)
=SUMIF(animals,C2&"*",legs)
=SUMIF(animals,"="&C2&"*",legs)
Regards, TMS
Brilliant - Thanks!!!
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks