0

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?

Improve this question
3
  • Are the month names on both the same? What I mean is there is two ways to get the month. One is to have a literal text string January. The second is a date that is formatted mmmm. Are both tabs the same type? If not you are trying to compare a number to text and will never match. Commented Sep 12, 2016 at 13:45
  • And it should only be 2 as there are only 2 rows where all three are 1 and the month is January. Commented Sep 12, 2016 at 13:49
  • Also did any of the answers in your last question: stackoverflow.com/questions/39415448/… work for you? If not then please give feed back so we can adjust to answer the question. If so please mark the one that worked as correct by clicking on the check mark by the answer. You will start seeing less people willing to help if no feedback is given. Commented Sep 12, 2016 at 14:01

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I believe I had that formula formatted this way last week, and it wouldn't work either so I switched it around and got this. I'll try to and post my results
I changed it to this and got "0", even when my criteria was either 0 or 1. The other formula worked when i switched the criteria is "0".
You need to show your data. There is something missing in your explanation.
The formula I gave works for me see edit. And see my comment above as to why yours is not working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.