Array Formula: Sum unique values with other conditions

[nuked]

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

Any advice appreciated

Thanks


</SPAN>

 

[schielrn]

Does this work?

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

 

[nuked]

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>

 

[schielrn]

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

 

[schielrn]

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

 

[nuked]

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?

 

[schielrn]

You are not getting 2 for the sample data you posted?

Excel Workbook
	A	B	C	D
15	James	2/13/2013		2
16	Gary	2/13/2013
17	James	2/13/2013
18	Barry	2/14/2013
19	Gary	2/14/2013
Sheet1

Cell Formulas
Range	Formula
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
	A	B	C	D
15	James	2/13/2013		3
16	Gary	2/13/2013
17	James	2/13/2013
18	Barry	2/14/2013
19	Gary	2/14/2013
20	Gary	2/15/2013
21	Gary	2/13/2013
22	James	2/13/2013
23	Adam	2/13/2013
Sheet1

Cell Formulas
Range	Formula
D15	=SUMPRODUCT(((range3&range4)<>"")*(range4=B15)/(COUNTIFS(range3,range3&"",range4,range4&"")))

 

[ArthriticPanda]

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

 

[schielrn]

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.

 

[nuked]

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))