# Array Formula: Sum unique values with other conditions

#### nuked

##### Well-known Member
Hi All,

Struggling to get my head around an array formula needed to count unique text values in a column, and one further condition in another column (same row).

I could VBA this in a second, but would rather find a formula if possible on this one.

I've found two good options for the unique record count:

{=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))}</SPAN>

Or the simpler (and quite brilliant)...</SPAN>

{=SUMPRODUCT((Range<>"")/(COUNTIF(Range,Range&"")))}

I've really struggled adding another condition to either frankly, in the way that I would with:

{=sum(1*(Range=''something")*(Range2="somethingelse"))}

Formulas not my strong point tbh

Thanks

</SPAN>

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Does this work?

=SUMPRODUCT(((range1&range2)<>"")/(COUNTIFS(range1,range1&"",range2,range2&"")))

I need a formula that counts the unique names on a given day (13/02/2013), so here, it would return 2.

 James 13/02/2013 Gary 13/02/2013 James 13/02/2013 Barry 14/02/2013 Gary 14/02/2013

<TBODY>
</TBODY>

I'll have to see if I can come up with something that includes a specific day after a bit.

Maybe this?

=SUMPRODUCT(((range1&range2)<>"")*(range2=datecell)/(COUNTIFS(range1,range1&"",range2,range2&"")))

Of course you will need to put in the correct range/cell references

Maybe this?

=SUMPRODUCT(((range1&range2)<>"")*(range2=datecell)/(COUNTIFS(range1,range1&"",range2,range2&"")))

Of course you will need to put in the correct range/cell references

Getting incorrect results. I think because I'm not dealing with the range2=datecell condition in the demoninator?

Last edited:
You are not getting 2 for the sample data you posted?
Excel Workbook
ABCD
15James2/13/20132
16Gary2/13/2013
17James2/13/2013
18Barry2/14/2013
19Gary2/14/2013
Sheet1
Cell Formulas
RangeFormula
D15=SUMPRODUCT(((range3&range4)<>"")*(range4=B15)/(COUNTIFS(range3,range3&"",range4,range4&"")))

And when I add some more data to test I am still getting correct results, what is giving you incorrect results?
Excel Workbook
ABCD
15James2/13/20133
16Gary2/13/2013
17James2/13/2013
18Barry2/14/2013
19Gary2/14/2013
20Gary2/15/2013
21Gary2/13/2013
22James2/13/2013
Sheet1
Cell Formulas
RangeFormula
D15=SUMPRODUCT(((range3&range4)<>"")*(range4=B15)/(COUNTIFS(range3,range3&"",range4,range4&"")))

You could try this array formula in cell G1 , where E1 contains the word 'James' and F1 contains the date '13/03/2013'

{=SUM((A1:A5=E1)*(B1:B5=F1))}

NB: use Ctrl+Shift+Enter tp apply the curly braces

You could try this array formula in cell G1 , where E1 contains the word 'James' and F1 contains the date '13/03/2013'

{=SUM((A1:A5=E1)*(B1:B5=F1))}

NB: use Ctrl+Shift+Enter tp apply the curly braces

That would only give you a count of James for a specific day? The OP is looking for in a unique person count per day.

Thanks for all the input. I got this version to work in the end:

=SUM(IF((range1=range1)*(range2=datecel),1/COUNTIFS(range1,range1,range2,datecel),0))

Replies
1
Views
133
Replies
14
Views
214
Replies
11
Views
670
Replies
9
Views
393
Replies
11
Views
270

1,203,269
Messages
6,054,482
Members
444,727
Latest member
Mayank Sharma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back