Sumproduct with multiple conditions

[digita]

Hi everyone,

In the formula below I used sumproduct function twice to count the items which are marked "N/A" in column E or "Completed" in column G. Items to be counted would also have a date recorded in column F.

=SUMPRODUCT((Sheet1!F4:F65536>0)*(Sheet1!E4:E65536="N/A"))+SUMPRODUCT((Sheet1!F4:F65536>0)*(Sheet1!G4:G65536="Completed"))

Can this be combined into one sumproduct formula?

Thanks in advance.

 

[Aladin Akyurek]

Hi everyone,

In the formula below I used sumproduct function twice to count the items which are marked "N/A" in column E or "Completed" in column G. Items to be counted would also have a date recorded in column F.

=SUMPRODUCT((Sheet1!F4:F65536>0)*(Sheet1!E4:E65536="N/A"))+SUMPRODUCT((Sheet1!F4:F65536>0)*(Sheet1!G4:G65536="Completed"))

Can this be combined into one sumproduct formula?

Thanks in advance.

Control+shift+enter, not just enter:

=SUM(
    IF(ISNUMBER(Sheet1!F4:F65536),
    IF((Sheet1!E4:E65536="N/A")+(Sheet1!G4:G65536="Completed")>0,
      1)))

 

[digita]

Brilliant. Thanks so much Aladin.

 

[Aladin Akyurek]

Brilliant. Thanks so much Aladin.

You are welcome. Thanks for providing feedback.