Forum Discussion
Countifs done in multiples
Hello,
We are new to this community, but not without experience in excel.
Our club has recently done a survey of our members, and we are trying to find when people are available and which of our activities most interest them. to do this, we are trying to use countifs statements arranged in a table, but excel seems to be only looking thourgh our list once. We have a total of 34 data points, and the grand total of the entire table is 34 people, which is the correct number of people. However, this number should be larger, as our respsonsees were able to select more than one option. The file is attached, and any help here would be much appreciated.
4 Replies
When one more variant with keeping asterisks as it is, the only point to correct misprint Satuday* -> Saturday*.
=SUMPRODUCT( (ISNUMBER(SEARCH(LEFT(F$32,LEN(F$32)-1),OFFSET($B$1,0,0,COUNTA($B:$B)+5))))* (ISNUMBER(SEARCH(LEFT($E33,LEN($E33)-1),OFFSET($C$1,0,0,COUNTA($B:$B)+5)))) )
I added OFFSET here to improve performance, that could be $B:$B and $C:$C accordingly.
If day of the week/name will be without asterisk it also shall work correctly, name without one character works.
That's in separate place in attached file.
- Hello:
The problem that you are having is that you are looking for the days of the week within a string. Therefore, a potential solution is to put an asterisk (*) also at the beginning of the day of the week: *Monday*; *Tuesday*... Etcetera.
I hope this helps.I was using the asterisks at the end of the day of the week, as you see in the photo, but I didn't think to put the asterisk at the front of the word as well. Thanks!
Good solution by Ferran Rodríguez. Simple and fits with Medieval Club UBCO existing knowledge.
Another option is a combination of Power Query and Pivot Table (if you have Excel 2010 - 2016 that is)
I realise this is no where as near as straightforward as Ferran's excellent fix, but I thought I'd share it anyway. It also avoids the risk of spelling mistakes not being picked up e.g. Satuday
