| 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 |
| 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 | |||
Hybrid Mode
