Odtnhj

I am looking to count how many of times a product was ordered. Simple enough if I was able to hard code the columns that correspond with their products but the columns often change. The pictures below give an idea of the data coming in, and my desired results. I want to count up the "Y" for each product and record how many different orders contained that item.

DISCLOSURE: My actual data set is a lot more complicated with thousands of records and hundreds of columns and not about fruit, but made this example for simplicity.

https://imgur.com/a/X5wtdh6

As you can see in this second example, the products are not the same or in the same order:
https://imgur.com/a/7TNmaHW

What I'm trying to do is create a formula that will check which column that product exists in, for example Oranges, and then sum up all the "Y" that are within that column. The product will at max be present as a column header once per file (sometimes it won't be in the file at all).

When manually gathering the values, I use this formula on a second sheet to create the summary table (using Oranges from Sample 1 as an example) =COUNTIF(Sheet1!B:B,"Y"). I manually change the column it is referencing each day we receive a new file. As you can imagine, this is very time-consuming.

In short, I am looking to replace the "Sheet1!B:B" part of the COUNTIF formula with a SEARCH (or equivalent) function to look which column contains Oranges. I am also open to a VBA solution, but am not as familiar with it.

Please let me know if you require any more clarification.

Thank you.

Was able to find this solution:

=COUNTIF(INDEX(Table1[#All],0,MATCH(A2,Table1[#Headers],0)),"Y")