Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CountIf Function
HI there
I have a current count IF function that works well. until now. The range is D4:D9, Usually it only searches numbers but now i need it to search 8a and 8b as values. Not the cells. What is the way around this. It would be more preferable to seach and add up 8a and 8a as values. (not the cells 8a and 8b) Thanks David |
#2
|
|||
|
|||
Hi David
One way: =COUNTIF(D4:D9,"8a") and for counting the numbers of 8a AND 8b =SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}")) -- Best Regards Leo Heuser Followup to newsgroup only please. "David Harrison" skrev i en meddelelse ... HI there I have a current count IF function that works well. until now. The range is D4:D9, Usually it only searches numbers but now i need it to search 8a and 8b as values. Not the cells. What is the way around this. It would be more preferable to seach and add up 8a and 8a as values. (not the cells 8a and 8b) Thanks David |
#3
|
|||
|
|||
"Leo Heuser" wrote in message ... and for counting the numbers of 8a AND 8b =SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}")) Typos apart, why introduce a nested function call? =SUMPRODUCT(--(D4:D9={"8a","8b"})) |
#4
|
|||
|
|||
"Bob Phillips" skrev i en meddelelse
... "Leo Heuser" wrote in message ... and for counting the numbers of 8a AND 8b =SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}")) Typos apart, why introduce a nested function call? Good question! I guess, I was caught by the subject line. How on earth did that quotation mark land after "}" instead of after "a" :-) |
#5
|
|||
|
|||
Hi Bob
The expression --(D4:D9={"8a","8b"}) returns a 2-dimensional (6 x 2) array where COUNTIF(D4:D9,{"8a,"8b"}") returns a 1-dimensional (1 x 2) array Either one might be useful depending on, which functions are to be used on them. To get the sum, either one may be used, and my solution may be less memory hungry than yours, depending on how Excel treats the functions internally. OTOH it uses one more function call. LeoH "Bob Phillips" skrev i en meddelelse ... "Leo Heuser" wrote in message ... and for counting the numbers of 8a AND 8b =SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}")) Typos apart, why introduce a nested function call? =SUMPRODUCT(--(D4:D9={"8a","8b"})) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using countif function to add only a half of a number | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
How do I use COUNTIF in a SUBTOTAL function to differentiate the . | Excel Worksheet Functions | |||
Advanced COUNTIF Function | Excel Worksheet Functions | |||
The countif function in Excel 2002. | Excel Worksheet Functions |