| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif adding AND
Need help.
I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Hmmm...
Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
T,
This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Hi try
=SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Friends,
These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Well of course it triggers the conditional format if B3="S" as you've added
+(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Thanks David.
Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
Maybe I am just not getting it.
I think you need to give us some more detail about what you're trying to do. You said this is for CF. What cell(s) are you wanting to format? Based on what condition(s)? OK, we've determined that B3=S is a separate condition. Does H3:N3 ever contain TEXT? Might there be formula blanks in the range? What's in G3? These are the kinds of details we need to know. -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
I would merely make the usual suggestion for diagnosing problems in a
formula. Break it up into manageable chunks and see what values you get. Put =B3="S" in a cell. What value do you get? Put =SUMPRODUCT((in a cell. What value do you get?)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 -- David Biddulph "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
T,
For example: I would like to CF cell C3 based on the contents of cells H3:N3 for cells C3:C37. If B3 = x light green If any cell in H3:N3 = N or L red If any cell in H3:N3 G3 pink If any cell in H3:N3 = G3 AND B3 = S yellow If any cell in H3:N3 = G3 AND B3 < S gray H3:N3 can contain Blanks, Text, or Numbers. G3 can contain Text or Numbers. Hope this helps. Respectfully, John Additionally: David, I am only an intermediate user, I hope the below helps you help me. Put =B3="S" in a cell. What value do you get? True Put =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) in a cell. What value do you get? #VALUE! Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? 0 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? 1 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Same as above, 1. If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 I dont understand this question/instruction. Respectfully, John "T. Valko" wrote: Maybe I am just not getting it. I think you need to give us some more detail about what you're trying to do. You said this is for CF. What cell(s) are you wanting to format? Based on what condition(s)? OK, we've determined that B3=S is a separate condition. Does H3:N3 ever contain TEXT? Might there be formula blanks in the range? What's in G3? These are the kinds of details we need to know. -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
David,
I am only an intermediate user, I hope the below helps you help me. Put =B3="S" in a cell. What value do you get? True Put =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) in a cell. What value do you get? #VALUE! Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? 0 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? 1 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Same as above, 1. If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 I dont understand this question/instruction. Respectfully, John Additionally, T, For example: I would like to CF cell C3 based on the contents of cells H3:N3 for cells C3:C37. If B3 = x light green If any cell in H3:N3 = N or L red If any cell in H3:N3 G3 pink If any cell in H3:N3 = G3 AND B3 = S yellow If any cell in H3:N3 = G3 AND B3 < S gray H3:N3 can contain Blanks, Text, or Numbers. G3 can contain Text or Numbers. Hope this helps. Respectfully, John "David Biddulph" wrote: I would merely make the usual suggestion for diagnosing problems in a formula. Break it up into manageable chunks and see what values you get. Put =B3="S" in a cell. What value do you get? Put =SUMPRODUCT((in a cell. What value do you get?)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 -- David Biddulph "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
If =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") and
=SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") each give 1, and if those formulae are what you used in your conditional formatting test, it should have triggered the conditional formatting. Are you sure that you used "Formula Is" in CF, not "Cell Value Is"? Are you sure that you did specify the format you want to use in the CF condition? [Sorry, the =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) was a copy & paste hiccup, but you've tested enough to see that the full condition is working.] -- David Biddulph "Johndb" wrote in message ... David, I am only an intermediate user, I hope the below helps you help me. Put =B3="S" in a cell. What value do you get? True Put =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) in a cell. What value do you get? #VALUE! Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? 0 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? 1 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Same as above, 1. If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 I don't understand this question/instruction. Respectfully, John Additionally, T, For example: I would like to CF cell C3 based on the contents of cells H3:N3 for cells C3:C37. If B3 = "x" light green If any cell in H3:N3 = "N" or "L" red If any cell in H3:N3 G3 pink If any cell in H3:N3 = G3 AND B3 = "S" yellow If any cell in H3:N3 = G3 AND B3 < "S" gray H3:N3 can contain Blanks, Text, or Numbers. G3 can contain Text or Numbers. Hope this helps. Respectfully, John "David Biddulph" wrote: I would merely make the usual suggestion for diagnosing problems in a formula. Break it up into manageable chunks and see what values you get. Put =B3="S" in a cell. What value do you get? Put =SUMPRODUCT((in a cell. What value do you get?)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 -- David Biddulph "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
What version of Excel are you using?
There is a limit to the number of CF conditions you can apply depending on the version of Excel. In versions prior to Excel 2007 the limit is 3 conditions. In Excel 2007 there is no number limit, the limit is system resources. If you're using a version of Excel prior to Excel 2007 then you'll have to use a macro to cover more than 3 conditions. -- Biff Microsoft Excel MVP "Johndb" wrote in message ... T, For example: I would like to CF cell C3 based on the contents of cells H3:N3 for cells C3:C37. If B3 = "x" light green If any cell in H3:N3 = "N" or "L" red If any cell in H3:N3 G3 pink If any cell in H3:N3 = G3 AND B3 = "S" yellow If any cell in H3:N3 = G3 AND B3 < "S" gray H3:N3 can contain Blanks, Text, or Numbers. G3 can contain Text or Numbers. Hope this helps. Respectfully, John Additionally: David, I am only an intermediate user, I hope the below helps you help me. Put =B3="S" in a cell. What value do you get? True Put =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) in a cell. What value do you get? #VALUE! Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? 0 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? 1 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Same as above, 1. If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 I don't understand this question/instruction. Respectfully, John "T. Valko" wrote: Maybe I am just not getting it. I think you need to give us some more detail about what you're trying to do. You said this is for CF. What cell(s) are you wanting to format? Based on what condition(s)? OK, we've determined that B3=S is a separate condition. Does H3:N3 ever contain TEXT? Might there be formula blanks in the range? What's in G3? These are the kinds of details we need to know. -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif adding AND
David,
I've got the original problem fixed now I think, thank you. However, now two out the five conditional formats I wrote aren't aren't working right. I believe this is my fault, and they just need rewriting. Using the base of, which works: =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") How would I write: =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") H3:N3="N" OR "L" with no reference to B3 at all. =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") H3:N3G3 with no reference to B3 at all. You've been of great help and patience, help me get the last mile. Thanks in advance, John "David Biddulph" wrote: If =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") and =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") each give 1, and if those formulae are what you used in your conditional formatting test, it should have triggered the conditional formatting. Are you sure that you used "Formula Is" in CF, not "Cell Value Is"? Are you sure that you did specify the format you want to use in the CF condition? [Sorry, the =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) was a copy & paste hiccup, but you've tested enough to see that the full condition is working.] -- David Biddulph "Johndb" wrote in message ... David, I am only an intermediate user, I hope the below helps you help me. Put =B3="S" in a cell. What value do you get? True Put =SUMPRODUCT((in a cell. What value do you get? H3:N3 ???)),(H3:N3=G3)) in a cell. What value do you get? #VALUE! Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? 0 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? 1 Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Same as above, 1. If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 I don't understand this question/instruction. Respectfully, John Additionally, T, For example: I would like to CF cell C3 based on the contents of cells H3:N3 for cells C3:C37. If B3 = "x" light green If any cell in H3:N3 = "N" or "L" red If any cell in H3:N3 G3 pink If any cell in H3:N3 = G3 AND B3 = "S" yellow If any cell in H3:N3 = G3 AND B3 < "S" gray H3:N3 can contain Blanks, Text, or Numbers. G3 can contain Text or Numbers. Hope this helps. Respectfully, John "David Biddulph" wrote: I would merely make the usual suggestion for diagnosing problems in a formula. Break it up into manageable chunks and see what values you get. Put =B3="S" in a cell. What value do you get? Put =SUMPRODUCT((in a cell. What value do you get?)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3)) in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? Put =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))*(B3="S") in a cell. What value do you get? If you still don't understand, you may need to go a step further and look at =ISNUMBER(H3) to =ISNUMBER(N3), and at =H3=$G3 to =N3=$G3 -- David Biddulph "Johndb" wrote in message ... Thanks David. Maybe I am just not getting it. The modification below causes the conditional format to never trigger. Suggestions? John "David Biddulph" wrote: Well of course it triggers the conditional format if B3="S" as you've added +(B3="S") to the SUMPRODUCT. That addition is effectively an OR function. If you wanted an AND function, try *(B3="S") instead of +(B3="S") -- David Biddulph "Johndb" wrote in message ... Friends, These formulas: =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") are still triggering the conditional format even though H3:N3 does not equal G3. Example: When G3=2 and H3:N3 contains no matching 2 and B3="S" it is still triggering the conditional format. This also occurs when H3:N3 contains zeroes and blanks. It should only trigger the conditional format when there is a matching value between G3 and any field within H3:N3. Thanks for everyone's help so far, any further ideas? Thanks for the effort, John "Eduardo" wrote: Hi try =SUMPRODUCT((ISNUMBER(H3:N3)),(H3:N3=G3))+(B3="S") "Johndb" wrote: T, This doesn't seem to work. I am using the formula to trigger a conditional format (amongst other conditional formats) and now =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") (h3:n3=g3) is not being evaluated before triggering the conditional format. Any other ideas, or am I screwing it up? John "T. Valko" wrote: Hmmm... Maybe this (normally entered): =SUMPRODUCT(--(ISNUMBER(H3:N3)),--(H3:N3=G3))+(B3="S") -- Biff Microsoft Excel MVP "Johndb" wrote in message ... Need help. I need to add an AND command to the following formula and can't seem to figure it out. =SUM(IF(ISNUMBER(H3:N3),IF(ISNUMBER(H3:N3),IF(H3:N 3=G3,1,0)))) and B3 = "S" The formula also needs to check if b3 is = to "S" Any assistance would be geatly appreciated. Warmest Regards, John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif formula isn't adding up right! | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Adding an AND Statement to COUNTIF | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Linear Mode
