Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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??


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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??


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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??




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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??


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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??







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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??


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How i can create a formula that combine subtotals and sumif Ray Excel Worksheet Functions 1 June 15th 06 04:17 PM
A formula maxif, similar with sumif or countif Dan Tacu Excel Worksheet Functions 1 June 6th 06 05:30 AM
Countif, Match or Sumif Formula Gunjani Excel Worksheet Functions 4 March 29th 06 05:22 PM
Which formula to use? countif, sumif, sumproduct zubee Excel Discussion (Misc queries) 3 September 2nd 05 08:16 PM
How do I create a formula in Excel that will countif or sumif bef. bkclark Excel Worksheet Functions 4 November 10th 04 06:30 PM


All times are GMT +1. The time now is 10:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"