Forum Discussion
Solved
Count Items in Classes of Pairs
I have a spreadsheet with nearly 9000 records in it. There are two columns of data: 1) Separator Installed and 2) BMPs Followed. Each record has either Y or N for each item. I want to summarize Certification Classes as follows: SYBY, SNBY, SNBY, and SNBN.
Using the CONCATENATE function, I have created a column with those values above. Then, I created a table displaying the totals using:
=COUNTIF($H$2:H$8812,"SYBY"), etc.
SYBY 8166
SYBN 248
SNBY 38
SNBN 359
8811
It seems there should be a way to write an COUNTIF to accomplish this without having the add another column of data. It would need to count if $D$2:$D8812 = "Y" AND if $E2:$E812 = "Y", etc. for the four 'classes' of Certification.
Thanks.
Hey Tom!
That's an interesting question. Hopefully I can help you out.
I'm thinking that SUMPRODUCT would be the best function to use here. Next to SYBY, I would enter
=SUMPRODUCT(($D$2:$D$8812="Y")*($E$2:$E$8812="Y"))
Similarly, I would enter the following next SYBN, SNBY, and SNBN respectively:
=SUMPRODUCT(($D$2:$D$8812="Y")*($E$2:$E$8812="N"))
=SUMPRODUCT(($D$2:$D$8812="N")*($E$2:$E$8812="Y"))
=SUMPRODUCT(($D$2:$D$8812="N")*($E$2:$E$8812="N"))
I think that should work for you. Let me know if it doesn't!
Hey Tom!
That's an interesting question. Hopefully I can help you out.
I'm thinking that SUMPRODUCT would be the best function to use here. Next to SYBY, I would enter
=SUMPRODUCT(($D$2:$D$8812="Y")*($E$2:$E$8812="Y"))
Similarly, I would enter the following next SYBN, SNBY, and SNBN respectively:
=SUMPRODUCT(($D$2:$D$8812="Y")*($E$2:$E$8812="N"))
=SUMPRODUCT(($D$2:$D$8812="N")*($E$2:$E$8812="Y"))
=SUMPRODUCT(($D$2:$D$8812="N")*($E$2:$E$8812="N"))
I think that should work for you. Let me know if it doesn't!
Excellent solution!
Worked perfectly!
