Count unique value with criteria & condition

[rachelwahid]

Dear Expert,
in my worksheet table (A1:C15) column A is date, B is item & C is quantity.

Formula 1:
I need a formula on F2 to get the unique value from column A considering E2 value.
Like E2 is May-2020, so i need result on F2 from A column unique date month of May-2020. but at calculation Column A unique date month of May-2020 should consider column C valid value. that means if column C any row will blank or Zero then that row column A is not come on my calculation.

Formula 2:
I need another formula on G5 which is also similar with Formula 1 But here i need result based on Criteria which is on F5 & my criteria range is column B & E5 is same like E2.

I have attached my sample worksheet where F2 & G5 i do manually for your ref.

Please help me in this regards.


Best Regards
Wahid

[bebo021999]

Try:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Please Login or Register  to view this content.

Array formula too

Though, my first formula counts 4 while yours is 3. Checking again, I see 4 is correct (in row 2,4,10,11)

[rachelwahid]

Try:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Please Login or Register  to view this content.

Array formula too

Though, my first formula counts 4 while yours is 3. Checking again, I see 4 is correct (in row 2,4,10,11)

Dear Expert,
Your formula is working properly but in formula 1 i dont need any relation with column B that was not required at post 1, thats why count mismatch between you and me. Please revise formula 1 without any relation with column B & other as is.

Formula 2 also i am describing again with details: first E5 will match the range Column A then F5 will match the column B then G5 will count how many unique data on column A considering last 2 condition except Column C blank or Zero value.

Please pay your valued attention.
Thanks

[bebo021999]

Dear Expert,
Your formula is working properly but in formula 1 i dont need any relation with column B that was not required at post 1, thats why count mismatch between you and me. Please revise formula 1 without any relation with column B & other as is.

OK try to replace range B by A:

Please Login or Register  to view this content.

[bebo021999]

Similar to formula 2 replace range C with range A:

Please Login or Register  to view this content.

[Bo_Ry]

Please try at
F2
=COUNT(1/FREQUENCY(IF((TEXT(A2:A15,"my")=TEXT(E2,"my"))*(C2:C15>0),A2:A15),A2:A15))

G2
=COUNT(1/FREQUENCY(IF((TEXT(A2:A15,"my")=TEXT(E5,"my"))*(C2:C15>0)*(B2:B15=F5),A2:A15),A2:A15))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).