Asking SUMPRODUCT to not count empty cells

[OverKnight]

Hello - I have a spreadsheet that contains the following formulae:

This formula counts how many Test 283 were pre-scheduled for Room01:
SUMPRODUCT(('Room01'!$H$6:$H$5001="TEST 283 OP")*('Room01'!$O$6:$O$5001="pre-sched"))

• Column H is a list of all of the procedures that could be performed in Room01. Test 283 is one of about 20.

• Column O is a list of which tests were pre-scheduled or were same-day add-ons.

This counts how many scheduled patients did not show up for any tests in Room01:
COUNTIFS('Room01'!I6:I5001,"",'Room01'!N6:N5001,"Sch")

• Column I is the time the patient arrived for testing; if this cell is blank, they did not show up for testing.

• Column N is a list of either scheduled (Sch) or arrived. Everyone (both pre-scheduled and add-on) are scheduled for their tests, so everyone starts as Sch. When they sign in for testing, the time entry in Column I changes the cell in Column N to arrived. If they do not show up for testing, i.e., no time entry in Column I, this cell remains Sch.

The problem is that if someone is scheduled for Test 283 and they do not show up, this is being counted by both formulae. The SUMPRODUCT formula works well for everything else I need to do; the only problem is I'm double-counting the scheduled tests and the no-shows, and I need for this formula to ignore the no-shows. Is there a way the SUMPRODUCT formula could be changed to ignore rows without an arrival time and count only those who showed up?

Thank you.

[Root_]

Try this:

=SUMPRODUCT((Room01!$H$6:$H$5001="TEST 283 OP")*(Room01!$O$6:$O$5001="pre-sched")*(Room01!$I$6:$I$5001<>""))

Or this:

=COUNTIFS(Room01!$H$6:$H$5001,"TEST 283 OP",Room01!$O$6:$O$5001,"pre-sched",Room01!I6:I5001,"<>")

In general, COUNTIFS works faster than SUMPRODUCT.

[OverKnight]

Thanks very much Root. I used your suggestion for the SUMPRODUCT formula in place of my current ones, and it worked perfectly. I have another section where I use COUNTIF for different data, and I realized that I also need to exclude no-shows from these. I was able to modify your COUNTIF formula to the data being counted, and again, it worked perfectly.

Thanks again.

[Root_]

You are most welcome.