Trying to use countif function for a heatmap...

BanderSnatch

New Member
Joined
May 16, 2016
Messages
4
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
Date Call GeneratedRaised By
01/04/2016JC
10/04/2016LM
13/04/2016James
22/04/2016LM
29/04/2016Louise
09/05/2016JC
15/06/2016LM
16/06/2016Louise
20/06/2016James

<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>
</tbody>

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

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,531
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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"}))
 

BanderSnatch

New Member
Joined
May 16, 2016
Messages
4
Brilliant! Thanks guys all 3 work perfectly.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,531
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For the record, the first one Special-K posted doesn't do what you want.
 

Forum statistics

Threads
1,085,294
Messages
5,382,772
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top