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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
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"}))
 
Upvote 0
For the record, the first one Special-K posted doesn't do what you want.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top