Counting unique entries with if condition

[Jigz]

Sample.JPG
Column A has duplicate values and Column F has a date in it.
I need to count total number of unique values from Column A which also meets the second condition of having some data in column F.
The formula should count total numbers of unique entries from Column A which has a date mentioned in column F, if the column F is blank then it should not count that entry.
Let me know if I need to ask more precisly, I can upload a screenshot. I am working on excel 2003.
I believe it should be some kind of a combination of sumproduct and if.
But I am not smart enought to figure this out. Please provide formula, thanks

[dilipandey]

Hi Jigz,

I believe you can use a pivot table to get your desired results.. upload a sample workbook so that I can give a quick try... thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[Jigz]

I've added a screet shot, I need the forumula for C28

[dilipandey]

Hi Jigz,

Suggest you to upload a excel workbook and I can't show you a pivot from a picture ...
If you want to give a quick try.. draw a pivot and have first column in row and second column count in data.. now where you have the data .. you'll get the answer
Now uncheck the data fields with blank entries .. I can think of this.. but would be confident if you upload a excel workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Jigz]

Excel file attached

[dilipandey]

Hi Jigz,

See the attached file and check the pivot and the green cells with answers.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[Jigz]

Sorry, but I don't see the attached file.

[dilipandey]

Oopss... attached below:-

Jigz -Sample(14).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[Jigz]

But I would still appreciate if this can be solved using formula. Because my original file has over 6000 rows and I am not confident on using this Pivot table...
I am preety sure there must be some kind of a combination of sumproduct, if and countif forumulas..
Thanks a lot for your help though..

[dilipandey]

Everything is already done there... you just need to refresh pivot (even which is now "Refresh on Open") and you are good to go... try adding more data and you'll find that this is working fine..

Regards,
DILIPandey


<click on below 'star' if this helps>

[FDibbins]

just to reinforce dilipandey's comments, a pivot table is much easier to work with, and, if set up properly, will give you the answers you need

[Jigz]

I completely agree, but still, if someone can provide a formula, it will be highly appreciated.

[Jigz]

If somebody can provide a formula, I would higly appreciate it.
Thanks

[FDibbins]

in cell B5, copied down, try this...

=IF(F5="","",IF(ISERROR(VLOOKUP(A5,A6:$A$22,1,FALSE)),1+COUNT($B$4:B4),""))

let me know how that works for you?

[Jigz]

Hi FDibbins, thannks for your efforts. I think we need little bit of fine tuning here... You are closed to solving this.
The result comes 1 less than the correct answer. It comes to 6 instead of 7.
Another problem with the above provided formula is when I enter a date in F10, column B is not adding 1 more count but when I add a date F11 it adds 1 more count. A10 and A11 are duplicate values and therefore, it should add 1 count when I put dates in either F10 or F11.
Thanks again

[Jigz]

Any help yet ??

[Jigz]

I am using following formula... but getting wrong answer..
Can I have some suggestion to make it work ?
=SUMPRODUCT((SUM(IF(FREQUENCY(MATCH(A16:A5750,A16:A5750,0),MATCH(A16:A5750,A16:A5750,0))>0,1)))*(AU16:AU5750<>""))