Countif/sumif too many arguments :d
=SUMIF(August!C:C,B20,August!H:H)
So I sum the H column if the C column matches Cell b20 - but how can I tell excel to;
sum the H column
if both the c column matches b20 AND the H column is greater than 50?
=SUMIF(August!C:C,B20,August!H:H,">50") <- is not working for me
Surely there is a way around this?
If you are using Excel 2007 or later, you can use SUMIFS,
=SUMIFS(August!H:H,August!C:C,B20,August!H:H,">50")
Or use SUMPRODUCT, which is compatible with ALL versions.
=SUMPRODUCT((August!H2:H100>50)*(August!C2:C100=B20),August!H2:H100)
Note: When use SUMPRODUCT do not use whole column reference like C:C, H:H use a limited range.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
since you have excel 2003 you will need to use sumproduct but you cant use whole ranges like h:h
=sumproduct(--(August!C1:C1000=B20),--(August!h1:h1000>50),(August!h1:h1000))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you for your time, but the following code is not working
=SUMPRODUCT((July!C1:C1000=B20),(July!H1:H1000>50),(July!H1:H1000))
even reverse
=SUMPRODUCT((July!C1:C1000=B20),(July!H1:H1000),(July!H1:H1000>50))
It is giving me a total of '0' instead of a much larger number. hmm any thoughts?
the 2nd comma shoud be eithe * or --
=SUMPRODUCT((July!C1:C1000=B20)*(July!H1:H1000>50),July!H1:H1000)
You are a champion :D!!!
Is there a way i can count these rows instead of summing them?
I am trying this but to no avail:
=SUM((COUNTIF(July!C:C,B3)*(July!C:C,>50))
use the sumproduct but leave of the sum range
=SUMPRODUCT((July!C1:C1000=B3)*(July!c1:c1000>50))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks