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

 

[XOR LX]

Hi.

Is there any chance you could re-do your example so that it includes a few more rows' worth of data? At the moment, it's difficult to understand your desired output: for one thing, in your example, each UserID occurs precisely once only in a given month. If it's actually possible that a given UserID can occur multiple times in a given month, could you amend the example to reflect this, and also indicate your desired results?

Regards

 

[Domenic]

Maybe...

A1:G6

[TABLE="width: 336"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Month
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]User ID
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Payment
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]123
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]124
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]123
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]125
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

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

<F2,$B$2:$B$6),0)),MATCH("~"&$B$2:$B$6,$B$2:$B$6&"",0))),ROW($B$2:$B$6)-ROW($B$2)+1)>=SUM(IF(FREQUENCY(IF($A$2:$A$6=F2,IF(ISNA(MATCH($B$2:$B$6,IF($A$2:$A$6 < F2,$B$2:$B$6),0)),MATCH("~"&$B$2:$B$6,$B$2:$B$6&"",0))),ROW($B$2:$B$6)-ROW($B$2)+1)>0,1))

Hope this helps!</F2,$B$2:$B$6),0)),MATCH("~"&$B$2:$B$6,$B$2:$B$6&"",0))),ROW($B$2:$B$6)-ROW($B$2)+1)>