SUMPRODUCT formula not working

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

Hi there,

I have this formula:

=SUMPRODUCT(--(R5:R200="May"),--(M5:M200="No"))

I'm trying to make it work so that it counts the number
of "No" in column M if "May" is in column R. But as it is
now, the result is always 0. I checked the format of the
cells and everything appears to be OK.

Can you help get this formula to work?

Thanks,
Kate
 
Hi Kate,

If Column R contains true dates, try:

=SUMPRODUCT(--(MONTH(R5:R200)=5),--(YEAR(R5:R200)=2004),--(M5:M200
="No"))

Hope this helps!
 
If you select a cell that says May, what do you see in the formula bar?
If you see May it should work if you don't have hidden characters in your
cells, if it says 5/1/2004 or something like that change the formula to

=SUMPRODUCT(--(MONTH(R5:R200)=5),--(M5:M200="No"))



--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
Hi
does your column A contain real date values? If yes use
=SUMPRODUCT(--(MONTH(R5:R200)=5),--(M5:M200="No"))
 
Kate,

Just as a thought, just try this

=SUMPRODUCT(--(TRIM(R5:R200)="May"),--(M5:M200="No"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top