Counting Unique values based on multiple criteria

[jaya]

I have been trying to use the following formula to count unique values in column 1 of my defined range, based on several criteria.

=SUMPRODUCT(--(INDEX(Consolidated,0,16)="SR")--(INDEX(Consolidated,0,16)="CR")--(INDEX(Consolidated,0,16)="TR"),--((LEFT(INDEX(Consolidated,0,15),2)="PR")*(MONTH(INDEX(Consolidated,0,24))=MONTH($B$1))*(INDEX(Consolidated,0,4)="C")),1/COUNTIF(INDEX(Consolidated,0,1),INDEX(Consolidated,0,1)))

The result appear to be unreliable i.e. it gives the correct result at time but gives the wrong result or fractions at other other times.

This is an adaptation of a formula which mikerickson posted about 2 years ago. Can anyone help me get this right please.

Thanks in advance

 

[WinteE]

Hi,

It would help a lot if you added an example of the data on which the formula is based.

Erik

 

[Adnym]

If you can't get your array formula to work you could do an advanced filter to extract unique values and then do a simple COUNT formula.

_____________________
Microsoft Excel Training

 

[Adnym]

Your fractions will be caused by this part of your formula

1/COUNTIF(INDEX(Consolidated,0,1)

________________________________
Kit Homes

 

[jaya]

Hi,

It would help a lot if you added an example of the data on which the formula is based.

Erik

Thanks Eric,

My data runs from columns A to AC. Row No.1 has the titles and the named range "Consolidated" covers the range columns A to AC and rows to 2 to the last row with data.

The type of data in the columns used in the formula are as follows:

Column 1 (A) = Job No. (Text)
Column 4 (D) = Work Status (Text)
Column 15 (O)= Task Title (Text)
Column 16 (P) = Work Type (Text)
Column 24 (X) = Actual End Date (Date)

Cell B1 has a date entry.

The strange thing is that the formula gives the correct result at times but not always.

Regards

 

[jaya]

Thanks Eric,

My data runs from columns A to AC. Row No.1 has the titles and the named range "Consolidated" covers the range columns A to AC and rows to 2 to the last row with data.

The type of data in the columns used in the formula are as follows:

Column 1 (A) = Job No. (Text)
Column 4 (D) = Work Status (Text)
Column 15 (O)= Task Title (Text)
Column 16 (P) = Work Type (Text)
Column 24 (X) = Actual End Date (Date)

Cell B1 has a date entry.

The strange thing is that the formula gives the correct result at times but not always.

Regards

By the way, my aim is to calculate the unique number of Jobs Nos (4) which have “CR”, “SR” or “TR” as the Work Type (16) with Task Title (15) beginning with “PR” and the Actual End Date (24) falls within the month of the date entry in cell B1.

 

[WinteE]

Hi jaya,

Try the formula below :

=SUMPRODUCT((P4:P8="TR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)+SUMPRODUCT((P4:P8="CR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)+SUMPRODUCT((P4:P8="SR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)

You probably have to change the row numbers '4' and '8'.

Success,

Erik

 

[jaya]

Hi jaya,

Try the formula below :

=SUMPRODUCT((P4:P8="TR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)+SUMPRODUCT((P4:P8="CR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)+SUMPRODUCT((P4:P8="SR")*(MONTH(X4:X8)=MONTH(B1))*(YEAR(X4:X8)=YEAR(B1))*(LEFT((O4:O8);2)="Pr")*1)

You probably have to change the row numbers '4' and '8'.

Success,

Erik

Thanks Erik.

1. Will your formula eliminate the duplicates in Column A? This has been my main issue. The "1/Countif(...." section of the formula was meant to do that.

2. I would like to retain the range name as my data range keeps changing. The range "Consolidated" is designed to adjust itself using an event procedure every time I copy and paste a new set of data.

Regards,

Jaya

 

[Aladin Akyurek]

I have been trying to use the following formula to count unique values in column 1 of my defined range, based on several criteria.

=SUMPRODUCT(--(INDEX(Consolidated,0,16)="SR")--(INDEX(Consolidated,0,16)="CR")--(INDEX(Consolidated,0,16)="TR"),--((LEFT(INDEX(Consolidated,0,15),2)="PR")*(MONTH(INDEX(Consolidated,0,24))=MONTH($B$1))*(INDEX(Consolidated,0,4)="C")),1/COUNTIF(INDEX(Consolidated,0,1),INDEX(Consolidated,0,1)))

The result appear to be unreliable i.e. it gives the correct result at time but gives the wrong result or fractions at other other times.

This is an adaptation of a formula which mikerickson posted about 2 years ago. Can anyone help me get this right please.

Thanks in advance

This will be an expensive formula...
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(INDEX(Consolidated,0,1)<>"",
   IF((INDEX(Consolidated,0,16)="SR")*(INDEX(Consolidated,0,16)="TR"),
   IF((LEFT(INDEX(Consolidated,0,15),2)="PR")*(MONTH(INDEX(Consolidated,0,24))=MONTH($B$1)),
   IF(INDEX(Consolidated,0,4)="C"),MATCH(INDEX(Consolidated,0,1),INDEX(Consolidated,0,1),0))))),
      ROW(INDEX(Consolidated,0,1))-ROW(INDEX(Consolidated,1,1))+1),1))

 

[jaya]

This will be an expensive formula...
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(INDEX(Consolidated,0,1)<>"",
   IF((INDEX(Consolidated,0,16)="SR")*(INDEX(Consolidated,0,16)="TR"),
   IF((LEFT(INDEX(Consolidated,0,15),2)="PR")*(MONTH(INDEX(Consolidated,0,24))=MONTH($B$1)),
   IF(INDEX(Consolidated,0,4)="C"),MATCH(INDEX(Consolidated,0,1),INDEX(Consolidated,0,1),0))))),
      ROW(INDEX(Consolidated,0,1))-ROW(INDEX(Consolidated,1,1))+1),1))

Thank you very much Aladin. It didn't work when I copied and pasted your formula. But I managed to rebuild it using your concept. Works perfectly. Your assistance is much appreciated.