| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif both conditions in a row of 2 cells exists
example of what I'm trying to do:
"if the number in G6 is <800 and H6 is =20 then count the row as 1" |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
Try this:
=(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
=sumproduct(--(g1:g999<800),--(h1:h999=20))
Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html newbie wrote: example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
That works Great! Now how could this be done with a range. For example,
looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
Then just use what Dave posted.
His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
I've tried it. Can't get it to work
"RagDyeR" wrote: Then just use what Dave posted. His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
Post what you tried.
newbie wrote: I've tried it. Can't get it to work "RagDyeR" wrote: Then just use what Dave posted. His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
tried a few more times. got it to work. Been looking at this thing too long.
Can I do something similar to just use 2 cells to be counted as a row only if they are not blank?" "Dave Peterson" wrote: Post what you tried. newbie wrote: I've tried it. Can't get it to work "RagDyeR" wrote: Then just use what Dave posted. His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
I don't understand what you're trying to say.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... tried a few more times. got it to work. Been looking at this thing too long. Can I do something similar to just use 2 cells to be counted as a row only if they are not blank?" "Dave Peterson" wrote: Post what you tried. newbie wrote: I've tried it. Can't get it to work "RagDyeR" wrote: Then just use what Dave posted. His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif both conditions in a row of 2 cells exists
maybe...
=sumproduct(--(g1:g999<""),--(h1:h999<"")) But this will treat formulas that return "" as "blank". newbie wrote: tried a few more times. got it to work. Been looking at this thing too long. Can I do something similar to just use 2 cells to be counted as a row only if they are not blank?" "Dave Peterson" wrote: Post what you tried. newbie wrote: I've tried it. Can't get it to work "RagDyeR" wrote: Then just use what Dave posted. His formula will total over a range. Follow his instructions to adjust the ranges he used to the ranges you are using. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "newbie" wrote in message ... That works Great! Now how could this be done with a range. For example, looking at each 2 cell row in a range and counting as 1 only if both condition exist. G6:G29 <800 and H6:H29=20 "RagDyeR" wrote: Try this: =(G6<800)*(H6=20) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "newbie" wrote in message ... example of what I'm trying to do: "if the number in G6 is <800 and H6 is =20 then count the row as 1" -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format cells with a formula (7 conditions). | Excel Discussion (Misc queries) | |||
| COUNTIF temporarily missed some cells | Excel Worksheet Functions | |||
| How do I use countif across non-adjacent cells? | Excel Worksheet Functions | |||
| Countif for specific cells rather than a range ???? | Excel Worksheet Functions | |||
| counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Linear Mode
