Sumproduct with multiple criteria on one range

问题:

In a dataset I have answers that participants to a survey gave. The answers are in one example numbered 1 to 5, with 1 being yes, and 2 to 5 being variants of no.

20 or so similar questions have been asked, and participants can be in either one of 20 subgroups. Questions were categorized into 6 classes.

Now the best way to go about such a dataset would normally be the use of a pivot-table, however the way the data is set up doesn't work with a pivot table, and due to the sheer size of the dataset remodelling isn't efficient.

To extract the amount of people in a certain subgroup that answered yes for questions in a certain class I use the following function:

=SuMPRODUCT(--(Test!D$4:$CC$1824=1)*(Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

In which Test!D$4:$CC$1824 is the range where answers are given, and the other two are ranges for subgroup and classes respectively.

By using --(Test!D$4:$CC$1824=1) I convert all data to 0's except for where participants answered yes (cell value = 1).

Now I would like to do the same thing for where they answered no, so the value is either 2 or 3 or 4 or 5. The ideal way would be to append some OR logic into the first test, coming about something like this: --(Test!D$4:$CC$1824={2,3,4,5})

Ofcourse this doesn't work, but is there any simple notation besides retyping the first part 4 times, and adding them together?

回答1:

The OR operation can be mimicked by adding all of the possibilities together.

=SuMPRODUCT(((Test!D$4:$CC$1824=2)+(Test!D$4:$CC$1824=3)+(Test!D$4:$CC$1824=4)+(Test!D$4:$CC$1824=5))*
            (Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

If there is any possibilitiy that two could be correct (in this case there isn't) wrap the sum in the SIGN function to get only zero or one.

=SuMPRODUCT(SIGN((Test!D$4:$CC$1824=2)+(Test!D$4:$CC$1824=3)+(Test!D$4:$CC$1824=4)+(Test!D$4:$CC$1824=5))*
            (Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

回答2:

I'd say you could just use >1 instead of =1

For selected results like 1 and 3 and 5 you probably need to add the sumproducts of each number.

Sidenote: the -- is not necessary anymore as it is just for converting true and false to 1 and 0 when there is only one bracket inside sumproduct