HELP COUNTING

[clk616]

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.

[#2]

@clk616 

In column H I typed this formula:

=MAX((A1="Yes")*(B1:F1="Yes"))

Drag it down and sum the resulting values.

[#3]

thanks Antony I am aware i can use the the method you have used but i am trying to do itin a single function step not in 2 separate functions.
for example: when i wanted to identify and count where all columns are "yes" (as i have had to elsewhere in the workbook) i used:
COUNTIFS('sheet1'!A:A,"YES",'SHEET1'!B:B,"YES",'SHEET1'!C:C,"YES",'SHEET1'!D:D,"YES",'SHEET1'!E:E,"YES",'SHEET1'!F:F,"YES")

the formula is placed on a front sheet which gives multiple answers and all the data is held on 6 other sheets in the workbook which are all interlinked, because of this i am trying to avoid having to 'total' a row and then 'total' the column created by the row.

hope that makes sense.

[#4]

@clk616 

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....

1. Open your file and press Alt+F11 to open VB Editor.
2. On VB Editor's Ribbon --> Insert --> Choose Module and this will insert a New Module called Module1.
3. Copy the code given below and paste it into the opened code window.
4. Close the VB Editor and save your file as Macro-Enabled Workbook.

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.

[#5]

@clk616 

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))

[#6]

@Sergei Baklan , that looks like a cool formula. I need to unpack it to see how it works. So much to learn. :)