Search criteria for multiple columns to sum multiple rows

[Amitsri_74]

I have a worksheet with 26 columns. Each column has a product. In each row there are bunch of Y or N indicating each customer having what product. For e.g.

Sum of
1 A B C D E F G H I J ...Cust
2 Prod A Prod B Prod C Prod D Prod E Prod F Prod G Prod H Prod I Prod J....
3 Y N Y N Y Y N Y N Y ... 230 4 N Y N Y N Y Y N Y Y ... 145

This sheet has over 250K rows for various combination of products for a set of customers. I am trying to use a search criteria which can provide me the sum of all customer (specific row).

My search criteria is in the column where I can input (below) Y or N or blank which would retrieve the sum (last column) of all rows meeting that combination. In the criteria if I choose blank then that column should be eliminated from the criteria meaning any value will be accepted.

Prod Criteria
Prod A Y
Prod B N
Prod B
Prod C N
Prod D Y
Prod E
Prod F
Prod G

I tried array formula and created a key for search but I am not able to concatenate them to search the specific row. Any help would be appreciated. I shortened the file for easier upload.

 

[jeffreyweir]

I think you should be able to get this directly simply by using a PivotTable, and dragging the Customer ID field into the Values pane, and making sure that you change the Value Field Settings to COUNT instead of SUM.

Give that a go. If you have any issues upload some Dummy data set out like your source data, which will make it easy for me to demonstrate what I mean.

 

[Amitsri_74]

Sometime, we tend to think too much and forget simple solutions like this (pivot table0. I used the pivot table with the customer count (using Sum) and used all the columns in the filter and that works. Thanks a bunch!!