SUMPRODUCT formula not working

[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

 

[Domenic]

Hi Kate,

If Column R contains true dates, try:

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

Hope this helps!

 

[Peo Sjoblom]

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

 

[Kate]

Reply didn't go through...please resend.

 

[Kate]

It spells out the word May, in general format. Still
doesn't work...

Kate

 

[Frank Kabel]

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

 

[Bob Phillips]

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)