CODEWITHSUNDEEP
excelfunctionexcel-formula
DukeLuke
DukeLuke

Reputation: 315

Excel CountIF nested in IF function

I have a dashboard I'm trying to put together and I don't understand how Excel is interpreting my formulas. I'm trying to get excel to read a cell that contains a month value, and if that value is true, then to read my dummy variables and count them. There is multiple criteria here so I used CountIFS.

Here's how the function works

=IF('Other tab'!$AI:$AI=B3,COUNTIFS('Other tab'!$BI:$BI,1,'Other Tab'!$BB:$BB,1,'Other tab'!$BC:$BC,1),"ERROR")

where any place there is a "1" there was a dummy variable created. I also tried to do this with the actual text that the dummy variables replaced and my formula still wouldn't work. I don't get an error, but the return value is "0"

Here's a sample of the data:
Current Tab:
1      A        B        C        D      E       F   G       H
2
3   Metric  January February    March   April   May June    July

Other tab:
    AI              BB          BC              BI
1 January           0            1               1
2 January           0            1               1
3 January           0            0               0
4 January           1            1               1
5 January           1            1               1
6 December          1            1               1

According to my formula, "3" should be the result here.

Update: I switched the formula to read the "0"'s on my dummy variables instead of the "1"'s and it worked to retrieve that, so why isn't it working when specifying "1" as my countif criteria?

Upvotes: 0

Views: 2121

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

There is no reason to put the first part in an IF statement just add it as criteria to the COUNTIFS:

=COUNTIFS('Other tab'!$AI:$AI,B3,'Other tab'!$BI:$BI,1,'Other Tab'!$BB:$BB,1,'Other tab'!$BC:$BC,1)

enter image description here

It appears that your data not consistent. You are looking for a text string in a series of dates that is formatted mmmm. To deal with this, in another column on the Other tab put this formula:

=TEXT(AI1,"mmmm")

And copy down.

Then change the 'Other tab'!$AI:$AI part in the above formula to new column in which you just put the 2nd formula.

This will now allow the comparison of text to text.

Upvotes: 1

Related Questions