Trying to use countif function for a heatmap...

[BanderSnatch]

Hi,

I have been trying (and failing!) to use the countif function for a heatmap.

In some cases there are two types of text data from a call log system that I need to extrapolate from the cells; I also need to do it between months of the year. For example;

C D[TABLE="width: 112"]
<colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"><tbody>[TR]
[TD="class: xl68, width: 75, bgcolor: #CC99FF"]Date Call Generated[/TD]
[TD="class: xl68, width: 75, bgcolor: #CC99FF"]Raised By[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]01/04/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]JC[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10/04/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]LM[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]13/04/2016[/TD]
[TD="class: xl69, bgcolor: transparent"]James[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]22/04/2016[/TD]
[TD="class: xl69, bgcolor: transparent"]LM[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]29/04/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]Louise[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]09/05/2016[/TD]
[TD="class: xl69, bgcolor: transparent"]JC[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]15/06/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]LM[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]16/06/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]Louise[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]20/06/2016[/TD]
[TD="class: xl66, bgcolor: transparent"]James[/TD]
[/TR]
</tbody>[/TABLE]

The formula i've tried is;

=COUNTIFS('Call Log'!$D$15:$D$999,"LM",'Call Log'!$D$15:$D$999,"Louise")*(MONTH('Call Log'!$C$15:$C$999)=4)

But the result is always coming back at 0. Am I missing something glaringly obviuous , is there anthing I can use in its place or do simpler? (for info the data starts at D15 and ends at D999).

Any help is gratefully received!

Thanks all

 

[Special-K99]

The conditions in COUNITFS are treated as ANDs
So youre saying if D15:D999 is LM...
AND
it is Louise

It cant be both. You need an OR

Try

=(COUNTIF('Call Log'!$D$15:$D$999,"LM")+COUNTIF('Call Log'!$D$15:$D$999,"Louise"))*(MONTH('Call Log'!$C$15:$C$999)=4)

 

[RoryA]

Welcome to the forum.

Your COUNTIFS won't work, because it requires both conditions to be true and a cell can't be both Louise and LM. Try:
=SUMPRODUCT((MONTH('Call Log'!$C$15:$C$999)=4)*('Call Log'!$D$15:$D$999={"LM","Louise"}))

 

[Special-K99]

Or

=SUMPRODUCT(($D$15:$D$999={"LM","Louise"})*(MONTH('Call Log'!$C$15:$C$999)=4))

 

[BanderSnatch]

Brilliant! Thanks guys all 3 work perfectly.

 

[RoryA]

For the record, the first one Special-K posted doesn't do what you want.

 

[BanderSnatch]

For the record, the first one Special-K posted doesn't do what you want.

You're right RoryA, I have modified and used for another report.

Thanks again guys.