Count Number of occurences Across Columns based on Similar Column Headers

**vidyuthrajesh** · 06-27-2013, 06:10 PM

Hello,

i have given the scenario and expected result from a formula. Can someone let me know if it is feasible and if so how can it be accomplished

Example:

A B C D E F G
Name Cat1 Cat2 Cat2 Cat1 Cat2 Cat1

1 Name1 Y Y N N Y Y

2 Name2 N N Y N Y y

3 Name1 Y Y N Y N Y

Expected Result

G4 = Name1 ( Input value)
G5 = Cat1 ( Input value)
G6 = Formula should help to : Count of Number of Y across ALL columns for Given value in G4 & G5 ( it should count all the Ys appearing in all Columns between B:G but it should count only if the Column Header = Value in G5 (Cat1 in this example) and Name=value in G4 ( Name1 in this example)
Thanks.

**FDibbins** · 06-27-2013, 06:14 PM

If I understand you correctly, take a look at using the countifS() function

**vidyuthrajesh** · 06-27-2013, 06:22 PM

Thanks for your response, but iam aware of countifs function but unsure if it can help in this scenerio. Countifs can take criteria range ( which can be only one colum, i guess) and a criteria but here i need to Count Number of Y's appearing on ALL Columns between B:G but the header ( row1) =cat1 and name(Column A)=Name1

Hope i am able to explain. Thanks.

**FDibbins** · 06-27-2013, 06:29 PM

Note, I suggested countifS(), not countif()

CountifS() allows for multiple criteria to base the count on...

=countifS(criteria_range1,criteria1,criteria_range2,criteria2,criteria_range3,criteria3.........................)

It can also be used with a range across multiple columns

If you upload a sample workbook, I will see what we can put together