Countif inquiry

Here's a formula to find the number of unique values, but i can't find out how to count only those with WTD next to it. Maybe someone else can add this?

=SUM(IF(INTERVAL(MATCH(A1:A100,A1:A100,0),MATCH(A1:A100,A1:A100,0))>0,1))

Originally Posted by L-Drr

Here's a formula to find the number of unique values, but i can't find out how to count only those with WTD next to it. Maybe someone else can add this?

=SUM(IF(INTERVAL(MATCH(A1:A100,A1:A100,0),MATCH(A1:A100,A1:A100,0))>0,1))

Anyway cartica, if you (or someone else) can somehow combine this formula with a LOOKUP (or something like that), the problem should be solved. Unfortunately I don't know how

Hold up I did find out!

=SUM(LOOKUP("WTD";B1:B100,A1:A100)*IF(INTERVAL(MATCH(A1:A100,A1:A100,0),MATCH(A1:A100,A1:A100,0))>0,1))

Oh, no, I see it's not working for me either

You did switch 2 arguments in the lookup, is that un purpose?
What exactly is not working? Are you getting an error as the wrong result?
Maybe I did something wrong in my translation from Dutch to English (both semantics and syntax are different)

djreddy
Forum rule number 2 (http://www.excelforum.com/forum-rule...rum-rules.html) states that you must start your own thread to ask your questions, do not use someone else's thread for that.

This is an array formula, adjust the range as needed:

=COUNT(1/FREQUENCY(IF(B1:B100="WTD",A1:A100),A1:A100))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by tdewale

You can also use =SUMPRODUCT((A1:A38>0)*(A1:B38="wtd"))

No, this counts EVERY value in column A that has WTD in column B, an answer of 11 based on the sample data. The answer desired is 3, the count of UNIQUE values that have WTD in column B.

Originally Posted by cartica

So I want it to return the number 3. I am looking for the unique number of days associated with "wtd".

It will return zero if you do not follow the instructions given to activate the array.

Enter the formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.


Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

The data in your column B "looks" like dates, but it's text. You need to convert those strings into dates.

1) Highlight column B
2) Select Data > Text To Columns > Delimited > Next > Next
3) On the third screen, click on DATE > DMY and click OK.

Now the formula will work.

I suppose you could record a macro of you doing the steps I outlined. Then you would have it done by just running the macro, fewer clicks.

Try this array formula, a similar function for TEXT.

=SUM(IF('EZE DATA'!$G$2:$G$559="WTD", 1/(COUNTIFS('EZE DATA'!$G$2:$G$559, "WTD", 'EZE DATA'!$B$2:$B$559, 'EZE DATA'!$B$2:$B$559)), 0))