Jul 01 2020 04:18 PM
A | B | C | D | E | F | |
YES | YES | |||||
YES | YES | |||||
YES | YES | YES | YES | YES | ||
YES | ||||||
YES | ||||||
YES | YES | YES | ||||
I am trying to right a formula that will count the "yes's" meeting the following criteria:
if yes in column A and at least one yes in columns c-f then count,
it must only count if there is a yes in column A and a yes in any or all the columns c-f.
I have been trying to use the countif/countifs but don't seem to be able to get it to work.
The above table would produce the result 3.
I know its probably a simple solution and i have tried combining countif with or, but can't get my head around it!
many thanks for any help.
Jul 01 2020 04:43 PM
In column H I typed this formula:
=MAX((A1="Yes")*(B1:F1="Yes"))
Drag it down and sum the resulting values.
Jul 01 2020 10:52 PM
Jul 02 2020 12:08 AM
If you are open to a VBA solution, you may construct your own User Defined Function which you can use on the Worksheet just like a Regular Excel Function.
To do so, follow these steps....
And you are good to go and use the User Defined Function called "CountYes" on the Worksheet.
So if your data is in the range A1:E6, place the following formula on the worksheet to get the desired count.
=CountYes(A1:E6)
Please refer to the attached with the UDF in place.
To view the code, press Alt+F11 to open VB Editor and double click on Module1 in the Project Explorer Pane on left side.
Jul 02 2020 01:46 PM
Variant with formula could be
with array constant hardocoded
=SUM(--(MMULT(--($B$1:$E$6="Yes"),{1;1;1;1})*($A$1:$A$6="Yes")>0))
or bit more flexible
=SUM(--(MMULT(--($B$1:$E$6="Yes"),SEQUENCE(COLUMNS($B$1:$E$6),1,1,0))*($A$1:$A$6="Yes")>0))
Jul 02 2020 04:58 PM
@Sergei Baklan , that looks like a cool formula. I need to unpack it to see how it works. So much to learn. :)