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

I current have a function like this;

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

This is okay it counts the number of story07s 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 dont know how to have the two functions in one. Can anyone possibly
advise??

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

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

"Natty" wrote:

I current have a function like this;

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

This is okay it counts the number of story07s 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 dont know how to have the two functions in one. Can anyone possibly
advise??

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

=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Natty" wrote in message
...
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??

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

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?
--
Nat


"Barb Reinhardt" wrote:

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

"Natty" wrote:

I current have a function like this;

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

This is okay it counts the number of story07s 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 dont know how to have the two functions in one. Can anyone possibly
advise??

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

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


"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Natty" wrote in message
...
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??

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

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?
--
Clark


"Barb Reinhardt" wrote:

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

"Natty" wrote:

I current have a function like this;

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

This is okay it counts the number of story07s 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 dont know how to have the two functions in one. Can anyone possibly
advise??

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

See

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

In article ,
Clark wrote:

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?

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

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


"JE McGimpsey" wrote:

See

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

In article ,
Clark wrote:

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?