Help - I need to combine both a SUMIF and COUNTIF into one formula

[Guest]

I current have a function like this;

=COUNTIF(G3:G1176,â€story07â€)

This is okay it counts the number of story07’s in that range

I also have a SUMIF;

=SUMIF(G3:G1176,â€story07â€,H3:H1176)

H is the fee, so this adds all the fees for story07 together.

What I would like to do is count the number of story07 in the range that
fall into income bands

So something like …

=COUNTIF(G3:G1176,â€story07â€) AND SUMIF(H3:H1176,<5000)

But I don’t know how to have the two functions in one. Can anyone possibly
advise??

 

[Guest]

Try
=sumproduct(--(G3:G1176="story07"),--(H3:H1176<5000),(H3:H1176))

 

[Bernard Liengme]

=SUMPRODUCT(--(G3:G1176="story07"),--(H3:H1176<5000))
will COUNT how many have story7 in G and less than 5000 in H
=SUMPRODUCT(--(G3:G1176="story07"),--(H3:H1176<5000),H3:H1176)
will SUM the H's for rows with story7 in G and less than 5000 in H

for more see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Guest]

Hi - thaks for that - I am getting a value now but it is the sum of the fees
not the number of fees. Any ideas who I can do this?

 

[Guest]

It works! Thanks very much! I didn't realise that you could put all of
those extra dashes in.

 

[Guest]

I am new to the boards and learning a lot here. But, I am confused by the
"--" in the below sumproduct formula. What does it mean?

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Guest]

Thanks a million! It new and high level stuff for me, but I printed it out.
Thanks again