Complex summing/counting of array - possible w/o macro?
Posted by Mike Fleuette on August 15, 2001 12:25 PM
I have an attendance list that tracks team sign-up and participation. For each date and member, I have a column that has values such as YES, NO, MAYBE, etc. Sometimes there are add'l comments for a particular person and day, such as 'YES - after 1PM'. I would like to count the different types of responses regardless of add'l comments. The pseudo-code would be (assuming data in B1:B10, types of responses in A12:A14 {YES,NO,MAYBE)):
B12: =COUNTIF(LEFT(B1:B10,LEN(A12)),A12) - but of course this doesn't work...
Help!
Posted by neo on August 15, 2001 12:49 PM
why not have an addtional column next to each response type just for comments, limiting the responses to 'yes, no, or maybe'? this way you can use a much more simplified formula such as =countif(b1:b10,"yes")
neo
Posted by Mark W. on August 15, 2001 12:58 PM
Okay, let's say that B1:B10 contains...
{"Yes; but later"
;"NOPE"
;"YEP"
;"Yes"
;"Hell Yes"
;"NEVER"
;"MAYBE"
;"WHEN HELL FREEZES
"
;"Yes sir!!!"
;"Whenever"}
...and A12:A14 contains {"YES";"NO";"MAYBE"}.
Enter the formula, =COUNTIF($B$1:$B$10,"*"&A12&"*"),
into cel B12 and Fill down.
