How to evaluate colums for a certain value and if criteria is met count it as one record?

[Internhelp]

Hello,

I have rows of data in Excel. I need a formula or function or whatever that will do the following:

Go row by row if at least any of the columns in 3 through 5 has a value as "Yes", Then count it as one record. Grouped by Age (20-30), (30-40),(40-50). So, this formula should answer how many 20-30-year-olds have at least one "Yes" as a value in Columns 3 through 5?


Please see my attachment with dummy data.

Thanks in advance!

 

[Michael M]

In column 6 put this formula then drag down as far as required

=IF(COUNTIF(C2:E2,"yes")>0,1)

Then put a sum at the bottm if you need a Total

 

[Saba Sabaratnam]

Or you could try this solution.

I have your data in the spreadsheet like this.

Enter the following formula in F2 and copy it down

=IF(COUNTIFS(C2:E2,"Yes")>=1,"Yes","No")

Create a bin from I3 to I7 and enter the following formula in J3.

=FREQUENCY(IF($F$2:$F$11="Yes",$B$2:$B$11,""),$I$3:$I$6)

I assume that you are using Office 365.

Kind regards

Saba

 

[Radoslaw Poprawski]

also yu can use Pivot Table.
group by in pivot table.
and either put yes/ no into columns or do some DAX formula