Sum distinct with multiple conditions

[nataliek92]

Hello

I am currently using Excel 2010, trying to calculte a sum based on multiple if statements.

I have data similar to the following :

Month \ User ID \ Payment
8 \ 123 \ £20
8 \ 124 \ £5
9 \ 123 \ £15
9 \ 125 \ £22
9 \ 126 \ £3

I would like to count the number of new User ID's each month. In August (8), there were 2 new users. In September (9) there were also 2 new users, as User ID 123 had made a payment in August.

I know how to count the number of distinct users overall, using the following formula :

=SUM(IF(FREQUENCY($B:$B,$B:$B)>0,1))

This would give the value 4.

I thought I would be able to amend this using a multiple IF statement, i.e.

=SUM(IF(AND(FREQUENCY($C:$C,$C:$C)>0,$E:$E=7),1))

But this gives the value 0, where I was expecting 2.

Basically I'm looking for a way to count the distinct User IDs in each month.

Does anyone know how to do this? Or could anyone offer any advice? It seems like it should be such a simple equation...

Thank you in advance,

Natalie

 

[steve the fish]

Try this. Enter CNTL-SHIFT-ENTER

=SUM(IF($A:$A=9,IF(FREQUENCY($B:$B,$B:$B)>0,1)))

 

[nataliek92]

Try this. Enter CNTL-SHIFT-ENTER

=SUM(IF($A:$A=9,IF(FREQUENCY($B:$B,$B:$B)>0,1)))

Thanks for your help, but unfortunately that also gives the value 0.

 

[steve the fish]

You need to enter CNTL-SHIFT-ENTER. Look for these {} around your formula. Its entered correctly then.

 

[nataliek92]

You need to enter CNTL-SHIFT-ENTER. Look for these {} around your formula. Its entered correctly then.

Yes sorry I tried that also. It gives #N/A...

 

[XOR LX]

Try this. Enter CNTL-SHIFT-ENTER

=SUM(IF($A:$A=9,IF(FREQUENCY($B:$B,$B:$B)>0,1)))

An array formula referencing entire columns is not a very good idea! Unlike e.g. SUMIF(S)/COUNTIF(S), this will be forced to calculate over all one million-plus cells, whether technically beyond the last used cell in those columns or not.

Regards

 

[steve the fish]

Ive no idea of the ranges it needs to be used on. What formula are you using and where is your data housed?

 

[Domenic]

Try the following...

A1:G6

Month	User ID	Payment
8	123	20			8	2
8	124	5			9	3
9	123	15
9	125	22
9	126	3

<TBODY>
</TBODY>

G2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=F2,$B$2:$B$6),IF($A$2:$A$6=F2,$B$2:$B$6))>0,1))

If you convert your data into a table (Ribbon > Insert > Table), the ranges will automatically adjust as data is added or removed.

Hope this helps!

 

[steve the fish]

Hello Dom. I don't think that's correct as I understood. I thought it was new users in month 9 so the answer should be 2.

 

[Domenic]

Hello Dom. I don't think that's correct as I understood. I thought it was new users in month 9 so the answer should be 2.

Hi Steve,

Yes, you're right. I missed it. Thanks for bringing it to my attention.

I'm logging off for now. So if you or anyone else offers a solution, great. Otherwise, I'll have a look at it when I get a chance.

Cheers!