Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How i can create a formula that combine subtotals and sumif | Excel Worksheet Functions | |||
A formula maxif, similar with sumif or countif | Excel Worksheet Functions | |||
Countif, Match or Sumif Formula | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |