Solved: SumUp Filtered Row

[hardcox]

Hi everyone, Im having a problem figuring out what will be the formula I am going to use bcoz I always got an error I read some articles and I guess only VBA can help me, though im not sure about that.

The problem goes like this I have 2 sheets. 1st is the raw data for my users and 2nd is the total data (sheet2) based from the raw data (sheet1). Attached is the workbook for your reference.

Questions.

1. How can I automatically sum up the error of each user (eg. Jane) in Error 1 and make the result appear on Sheet2? I tried this but dont work;
[vba]=countifs(a2:a20, "Jane", Sum(b2:b20))[/vba] and i suspect that it will sumUp all the value in Error A, I need each user to be sumUp.

2. How can I count the total in Commit? Because it should be counted like this. All that has the unique inputs will be considered as one count. Example on Jane, all num "1" is counted as 1 and all num "2" is again counted as 1. The result in Commit should be = 3 since there is 3 unique number. My problem is the input are repeated so I have problem summing it up.

I appreciate all your help.

Thank you.

[Bob Phillips]

1. How can I automatically sum up the error of each user (eg. Jane) in Error 1 and make the result appear on Sheet2? I tried this but dont work;
[vba]=countifs(a2:a20, "Jane", Sum(b2:b20))[/vba] and i suspect that it will sumUp all the value in Error A, I need each user to be sumUp.

=SUMIFS(B2:B20,A2:A20,"Jane")

2. How can I count the total in Commit? Because it should be counted like this. All that has the unique inputs will be considered as one count. Example on Jane, all num "1" is counted as 1 and all num "2" is again counted as 1. The result in Commit should be = 3 since there is 3 unique number. My problem is the input are repeated so I have problem summing it up.

Which column is being counted?

[hardcox]

Column D is being counted. I want to sumup only unique or distinct values, so all number 1 is counted as 1 and all number 2 is counted as 1 again.

[hardcox]

I think its somehow like this but it doesnt work
[vba]
=SUM(IF(FREQUENCY(D325, D225)>0,1, A2:A25, "Jane"))
[/vba]

[Bob Phillips]

Did you array-enter it (Ctrl-Shift-Enter)?

[hardcox]

Yes and it says too many arguements. This works well
[vba]=SUM(If(FREQUENCY(D325, D225)>0,1,))[/vba]
only that how can i refer it to a user eg "Jane"?

[Bob Phillips]

Try this array formula

SUM(--(FREQUENCY(IF($A$2:$A$25="Jane",MATCH($D$2:$D$25,$D$2:$D$25,0)),ROW(INDIREC T("1:"&ROWS($D$2:$D$25))))>0))

[Bob Phillips]

PS watch that space that the forum inserted in INDIRECT

[hardcox]

ok i will try that.. Thank so much.

[hardcox]

Thanks XLD it works. Ill test for a while with different datas.

[hardcox]

XLD, sorry to bother you again but I think I forgot something very important. There are still one condition I forgot to include. The "date". Using this array formula;
[vba]
SUM(--(FREQUENCY(IF($A$2:$A$25="Jane",MATCH($D$2:$D$25,$D$2:$D$25,0)),ROW(INDIREC T("1:"&ROWS($D$2:$D$25))))>0))
[/vba]
I was able to count unique number only that there is a same number in Column D which has different date, so if unique number date is not equal it should be counted as one again. In short those duplicated number which has the same dates are counted as one, if the date is different it should be counted as another one even though it is the same number. Please see attached file for your reference. Again thank you.

[Bob Phillips]

Are you saying you want to count the number where column D&E are unqiue? What number do you expect with that data?

[hardcox]

Yes something like that. Example

User Commit Date
Jane 1 2-12-12
Jane 1 2-12-12
Jane 1 2-13-12
Jane 1 2-13-12
Jane 2 2-12-12
Jane 2 2-12-12
Jane 2 2-13-12
Jane 1 2-12-12
Jane 1 2-12-12

Total Commit = 4

In the data there is six number 1, four of it has the same date so it should be counted as one and two of it has also the same date different from the other 1, so it should be counted as another 1. So as the number 2, there is three number two in commit column but 1 of it has different date so it should be counted as 1 again, so the total commit is 4. Something like this.

If Commit is equal to Date then it is counted as 1
If Commit is not equal to Date it also counted as 1 even though Commit has the same value.

[Bob Phillips]

Try this

=SUM(--(FREQUENCY(IF($A$2:$A$25="Jane",MATCH($D$2:$D$25&$E$2:$E$25,$D$2:$D$25&$E$2 :$E$25,0)),ROW(INDIRECT("1:"&ROWS($D$2:$D$25))))>0))

[hardcox]

Works like a charm, exactly what I wanted to do. Thanks for the quick response.