Count cells if any criteria are met, not multiple criteria

[jesstaylor]

So I need to count the number of values in a column (G), only if the cells in columns A-F have a value in them. If any (or multiple) cell in A-F has a value in it, (G) should be counted. I have attached a dummy file with a sample of what I'm looking for with this.

I've tried COUNTIFS and SUMPRODUCT functions with no luck, I'm sure it's just that I'm not sure how to format it to make it work.

Helper columns are okay as long as I can hide them later I can make pretty much anything work with this workbook.

I am using one cell to total the count of these and the cell range in the actual sheet is: S2:X21, each cell will either be blank or have a 1 in them. The cells counted in the actual cell are AG2:AG21. This value will be calculated in cell AK9. This is a large work file so I would love to make it work as soon as possible.

Any help is appreciated. Thank you!

[mcmahobt]

Do you want to sum the values in G if the other cells are unpopulated, or simply count how many cells fit the criteria?

[jesstaylor]

Just a count of them. The values in G are useless, they are cross-reference numbers in the actual sheet.

Thanks.

[mcmahobt]

Not the most elegant, but if you are able to include a helper column, this should work for any text that populates a cell, and should be easy to implement in a bigger file. Hope this helps!

[Jonmo1]

Here's another method with a helper column

In H2 and filled down
=ISNUMBER(MATCH(1,A2:F2,0))

Then use
=SUMPRODUCT(--H2:H9,--(G2:G9<>""))

[jesstaylor]

Jonmo1 - Thank you! Your formulas worked great!

mcmahobt - Thank you for your help! Your way would have worked too, I just have OCD about errors and I'm too lazy to put it in an iferror function

Thank you both for the quick replies!

[Jonmo1]

Glad to help...

Come to think about it, no need for testing for error (which is basically what ISNUMBER is doing)...

You can use this in H2 and filled down
=COUNT(A2:F2)
Then

=SUMPRODUCT(--(H2:H9>0),--(G2:G9,<>0))