| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with multiple criteria
Hi ALL,
In cells E4:E12 have the next data: 80 0 80 #N/A 300 50 100 250 Need COUNTIF formula to count the number of not empty cells different of 0 and #N/A in the range E4:E12. The right answer should be 6. Tried next formulas with no success: =COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1 which obviously is wrong. =SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A =FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A Any help is highly appreciated as always. Tim |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif with multiple criteria
Based on your sample data all being positive values:
=COUNTIF(E4:E12,"0") If there might be negative values: =COUNT(E4:E12)-COUNTIF(E4:E12,0) -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi ALL, In cells E4:E12 have the next data: 80 0 80 #N/A 300 50 100 250 Need COUNTIF formula to count the number of not empty cells different of 0 and #N/A in the range E4:E12. The right answer should be 6. Tried next formulas with no success: =COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1 which obviously is wrong. =SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A =FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A Any help is highly appreciated as always. Tim |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif with multiple criteria
Simple and perfect.
Thank you Biff!!! Tim "T. Valko" wrote: Based on your sample data all being positive values: =COUNTIF(E4:E12,"0") If there might be negative values: =COUNT(E4:E12)-COUNTIF(E4:E12,0) -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi ALL, In cells E4:E12 have the next data: 80 0 80 #N/A 300 50 100 250 Need COUNTIF formula to count the number of not empty cells different of 0 and #N/A in the range E4:E12. The right answer should be 6. Tried next formulas with no success: =COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1 which obviously is wrong. =SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A =FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A Any help is highly appreciated as always. Tim |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif with multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tim" wrote in message ... Simple and perfect. Thank you Biff!!! Tim "T. Valko" wrote: Based on your sample data all being positive values: =COUNTIF(E4:E12,"0") If there might be negative values: =COUNT(E4:E12)-COUNTIF(E4:E12,0) -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi ALL, In cells E4:E12 have the next data: 80 0 80 #N/A 300 50 100 250 Need COUNTIF formula to count the number of not empty cells different of 0 and #N/A in the range E4:E12. The right answer should be 6. Tried next formulas with no success: =COUNTIF(E4:E12,"<""")-COUNTIF(E4:E12,"<#N/A") gives me as answer 1 which obviously is wrong. =SUMPRODUCT(--(E4:E12<""),--(E4:E12<"#N/A")) gives me as answer #N/A =FREQUENCY(E4:E12,{"","#N/A"}) gives answer #N/A Any help is highly appreciated as always. Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif with multiple criteria | Excel Discussion (Misc queries) | |||
| Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
| Countif, Multiple criteria | Excel Discussion (Misc queries) | |||
| Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
