COUNTIFS with multiple criteria, including "OR"

[Wanderarzt]

Dear all,

Could you please advise on how I can make this work?

I am using google sheets. The excel file is here: https://docs.google.com/spreadsheet...9dvpA_dBjk/pubhtml?gid=1919792096&single=true

I want to calculate a number of all the raws, where cells in A column are not empty (i.e., in my case - have date in them). But the second criteria is to calculate the number of only those rows with anything in column A, where column C or E also has something.

Working formula is COUNTIFS(A2:A1000, "<>", C2:C1000, "<>", E2:E1000, "")+COUNTIFS(A2:A1000, "<>", C2-C1000, "", E2:E1000, "<>")+COUNTIFS(A2:A1000, "<>", C2:C1000, "<>", E2-E1000, "<>")
This formula is bulky, however, and it is too much complicated when I need to employ more columns.

So I only want count any row once, if it meets at least one criteria. And not count it twice just because it has values in both row C and row E. Row A, however, is a must criteria and is above all.

I have found out that probably "SUM" or "SUMPRODUCT" is the key, but I failed to understand how it works.
How do I make it work, please?

Thank you!

 

[Eric W]

Welcome to the Board.

Try:

=SUMPRODUCT(--(A2:A1000<>""),--((C2:C1000<>"")+(E2:E1000<>"")>0))

 

[Wanderarzt]

Hi, Eric! Thank you!

That worked perfectly. Is that magic?
Good day!

 

[Eric W]

Not magic, just Boolean algebra! The + is the equivalent of OR. Glad it works for you!