How to count cell value with multiple criteria while ignoring duplicate

[jomi9501]

Hi everyone, please help me create an array formula that will count the cell value with multiple criteria while ignoring duplicate.

Criterias:
1. Look at column G for the date that belong on the month specified in column A
2. Column H must not be empty

See attached file. Expected output is in column B highlighted in yellow

[jomi9501]

See attached for corrected file with value on column Hcount ignoring duplicate corrected.xlsx

[stunn]

First create a helper column by using this formula in I2 to indicate unique records (copy it downward):
=SUMPRODUCT(($F$2:$F2=$F2)*($G$2:$G2=$G2)*($H$2:$H2=$H2))=1

Then you can use this in C2 (copied downward):
=SUMPRODUCT((YEAR($G$2:$G$4000)=YEAR($A2))*(MONTH($G$2:$G$4000)=MONTH($A2))*($H$2:$H$4000<>"")*$I$2:$I$4000)

[jomi9501]

Thank you for the array formula but it gives different result (expected result in 2nd column). See below

Mar-11 4 2
Apr-11 1 1
May-11 0 0
Jun-11 2 1
Jul-11 1 1
Aug-11 6 2
Sep-11 3 2
Oct-11 6 5
Nov-11 2 1
Dec-11 1 0
Jan-12 5 3
Feb-12 2 0
Mar-12 12 9
Apr-12 9 3
May-12 24 16

[stunn]

I'm struggling to see how you get those expected results from the data and requirements that you gave. If no one else provides a better answer, you may need to explain further.

[jomi9501]

Correction on the expected result. See below in column B

Mar-11 1 2
Apr-11 1 1
May-11 0 0
Jun-11 1 1
Jul-11 1 1
Aug-11 5 2
Sep-11 1 2
Oct-11 5 5
Nov-11 1 1
Dec-11 0 0
Jan-12 2 3
Feb-12 1 0

One of the criteria is that column H must not be empty. It contains data of "Closed" & "Open". The formula on column I will give different result if column H contains either closed or open while the result must count on column F which will ignore duplicate as long as it satisfy the required month & column H is not empty.

[Haseeb Avarakkan]

If Reference numbers contains only numbers, try this Array formula in B2 with CTRL+SHIFT+ENTER, yhen copy down.

=SUM(IF(FREQUENCY(IF(TEXT(G$2:G$3672,"mmmyyyy")=TEXT(A2,"mmmyyyy"),F$2:F$3672+0),F$2:F$3672+0),1))

[jomi9501]

Thanks Haseeb, it answer the 1st expected output wherein column H was not considered. On my latter post, the criteria includes that column H must not be empty to consider it counting the unique values in column F.

[Haseeb Avarakkan]

On your sample file, there is no data in column H

Anyway, you can add one more IF to check H$2:H$3672<>""

=SUM(IF(FREQUENCY(IF(TEXT(G$2:G$3672,"mmmyyyy")=TEXT(A2,"mmmyyyy"),IF(H$2:H$3672<>"",F$2:F$3672+0)),F$2:F$3672+0),1))

[jomi9501]

Thank you, that's very helpful.