MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.