Summing days that match a criteria and date

[Saylindara]

I have a list of referrals which includes date of appointment, status (e.g.
urgent, non-urgent etc.), appointment date, waiting time (number of days
from appointment received to appointment date). Thanks to help already
received I can count the number of referrals received in a month/year that
match a particular status. What I would like to do now is find the average
waiting times for that month/year. For a start I’ve been trying to sum the
waiting time days using variations of the following:

SUMPRODUCT(--MONTH(Referrals!$C$4:$C$1200)=MONTH(B$2),
--YEAR(Referrals!$C$4:$C$1200)=YEAR(B$2),--(Referrals!$E$4:$E$1200=â€urgentâ€),
Referrals!$I$4:$I$1200)

C:C is the appointment received date, B2 is the month/year on the statistics
sheet, E:E contains the status, and I:I the waiting time in days.

Other versions of this formula resulted in blithering idiot messages but
this one politely returns 0 which I know is wrong. Any help would be
appreciated.

 

[Max]

.. this one politely returns 0 which I know is wrong
The sum range in your expression (ie col I in Referrals) may contain text
nums. You could try an "add zero" to coerce any text nums within the sum
range to real nums, viz. make it: ...,Referrals!$I$4:$I$1200+0)
Any good? hit the YES below

 

[Joe User]

SUMPRODUCT(--MONTH(Referrals!$C$4:$C$1200)=MONTH(B$2),
--YEAR(Referrals!$C$4:$C$1200)=YEAR(B$2),--(Referrals!$E$4:$E$1200=â€urgentâ€),
Referrals!$I$4:$I$1200)

If you copy-and-pasted the formula from the Formula Bar to your posting --
that is, if you have no typos in the posting -- you are missing parentheses
around the first two terms. That causes the first two terms to become
arrays of TRUE and FALSE, which SUMPRODUCT treats as zero. (From the Help
page: "SUMPRODUCT treats array entries that are not numeric as if they were
zeros".)

The expression should be written as follows (copy-and-paste from here into
the Formula Bar in your worksheet):

SUMPRODUCT(--(MONTH(Referrals!$C$4:$C$1200)=MONTH(B$2)),
--(YEAR(Referrals!$C$4:$C$1200)=YEAR(B$2)),
--(Referrals!$E$4:$E$1200=â€urgentâ€), Referrals!$I$4:$I$1200)


----- original message -----

 

[Saylindara]

Brilliant! Thank you.

If you copy-and-pasted the formula from the Formula Bar to your posting --
that is, if you have no typos in the posting -- you are missing parentheses
around the first two terms. That causes the first two terms to become
arrays of TRUE and FALSE, which SUMPRODUCT treats as zero. (From the Help
page: "SUMPRODUCT treats array entries that are not numeric as if they were
zeros".)

The expression should be written as follows (copy-and-paste from here into
the Formula Bar in your worksheet):

SUMPRODUCT(--(MONTH(Referrals!$C$4:$C$1200)=MONTH(B$2)),
--(YEAR(Referrals!$C$4:$C$1200)=YEAR(B$2)),
--(Referrals!$E$4:$E$1200=â€urgentâ€), Referrals!$I$4:$I$1200)


----- original message -----



.