Count cells matching two criteria (DATE & TEXT)

  • Thread starter truancyreductioncenter
  • Start date
T

truancyreductioncenter

Hi,
I work for a Truancy Reduction Center. We receive referrals from ove
50 schools in the district for kids who are truant. We'd like to kno
the number of referrals a specific school sent us during a specifi
month. Referral Dates are in F4:F500 and School Names are G4:G500.
This is what I've been trying:
=SUMPRODUCT(--(TEXT('Client Lo
09-10'!f4:f500,"yyyymm")="200910"),--('Client Lo
09-10'!G4:G500="pacifica"))

These functions work separately, but don't seem to want to pla
together. I just read a suggested related thread talking about Wil
Cards? Thanks for all your help!
 
D

Dave Peterson

Your formula looks ok to me--but it's usually better to copy directly from the
formula bar and paste into the message (instead of retyping).

I'd guess that your data isn't what you expected--maybe the year is not 2009 or
maybe you have extra (leading/trailing) spaces in the name field.


Hi,
I work for a Truancy Reduction Center. We receive referrals from over
50 schools in the district for kids who are truant. We'd like to know
the number of referrals a specific school sent us during a specific
month. Referral Dates are in F4:F500 and School Names are G4:G500.
This is what I've been trying:
=SUMPRODUCT(--(TEXT('Client Log
09-10'!f4:f500,"yyyymm")="200910"),--('Client Log
09-10'!G4:G500="pacifica"))

These functions work separately, but don't seem to want to play
together. I just read a suggested related thread talking about Wild
Cards? Thanks for all your help!!

--
truancyreductioncenter
------------------------------------------------------------------------
truancyreductioncenter's Profile: 1443
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=174708

Microsoft Office Help
Click to expand...
 
D

Dave Peterson

=SUMPRODUCT(--(TEXT('Client Log 09-10'!f4:f500,"yyyymm")="200910"),
--('Client Log 09-10'!G4:G500="pacifica"))

If you were counting the number of dates in October of 2009 for the schools
"Pacifica West Campus" and "Pacifica East Campus", you could use:

=SUMPRODUCT(--(TEXT('Client Log 09-10'!f4:f500,"yyyymm")="200910"),
--(left('Client Log 09-10'!G4:G500,8)="pacifica")

Or if you were counting the number of dates in October of 2009 for all the
schools with Pacifica in the name:

=SUMPRODUCT(--(TEXT('Client Log 09-10'!f4:f500,"yyyymm")="200910"),
--(isnumber(search("pacifica",'Client Log 09-10'!G4:G500))))

Use =Find() instead of =Search() if upper/lower case matters.
Hi,
I work for a Truancy Reduction Center. We receive referrals from over
50 schools in the district for kids who are truant. We'd like to know
the number of referrals a specific school sent us during a specific
month. Referral Dates are in F4:F500 and School Names are G4:G500.
This is what I've been trying:
=SUMPRODUCT(--(TEXT('Client Log
09-10'!f4:f500,"yyyymm")="200910"),--('Client Log
09-10'!G4:G500="pacifica"))

These functions work separately, but don't seem to want to play
together. I just read a suggested related thread talking about Wild
Cards? Thanks for all your help!!

--
truancyreductioncenter
------------------------------------------------------------------------
truancyreductioncenter's Profile: 1443
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=174708

Microsoft Office Help
Click to expand...
 
T

teylyn

Hi,

just yesterday I've done an analysis of the error codes returned by
Sumproduct and what their underlying reasons are. Since you don't state
the error you are getting, maybe you want to look at the file and see
what might apply in your case. If that does not help, please check
back.

see attachment.


+-------------------------------------------------------------------+
|Filename: Sumproduct error messages.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=421|
+-------------------------------------------------------------------+
 
T

truancyreductioncenter

The solution was found but another problem arose. Luckily Dave's pos
provided the answer. Thanks to all who contributed!

What I overlooked was I did not have ranges the same. Like Dave firs
posted, I retyped the formula for the post, and didn't copy directl
from excel, therefore the posted formula was correct and worked. Sorr
about that!

HOWEVER, once I went and applied the formula to every individua
school, I was getting different results to the total referrals pe
school. HOWEVER again, hehe, the TOTAL YTD number of referrals wa
still the same give or take one (a duplicate) WEIRD! -It's a share
workbook so I believe I can't save the single sheet (the rest i
confidential), but if there's a way please let me know. -
Anyways, I used Dave's

=SUMPRODUCT(--(TEXT('Client Log 09-10'!f4:f500,"yyyymm")="200910"),
--(isnumber(search("pacifica",'Client Log 09-10'!G4:G500))))

and it worked JUST fine. So thanks again to everyone! And Teyly
thanks for the info I'll definitely keep it for future support
 
Top