Searching And Summing

[John_Efc]

Hi there,

Quick question for help here....

I have a spreadsheet where I have the following headings:

___A____________B_________C________D________E_____________F
1_username____status____before____after____total changes___total same.
2_AAAA________A_________A________A__________0____________1
3_AAAA________A_________A________A__________0____________1
4_AAAA________A_________A________A__________0____________1
5_AAAA________A_________A________A__________0____________1
6_AAAA________A_________A________A__________0____________1
7_AAAA________A_________A________A__________0____________1

I have total changes so that it shows 1 when before isnt the same a
after.
Total same is when before and after are the same.

What I need to do now is come up with a way so that I can total change
and sames for the different usernames by searching for the username.

I have this code

C2 (This is cell of username) K2 (input of username to search)

=IF(AND(K2=C2;F2="1");"1";"0")

So it will put a 1 when the username matches and also total same is 1.

But this only works for row 2.

Is there a way I could use the same principle but reference A1-A7 fo
the username and total the 1s in F1-F7.

So above would return 6 because AAAA has 6 sames?

Thank

 

[shanermuls]

Hi there,

Quick question for help here....

I have a spreadsheet where I have the following headings:

___A____________B_________C________D________E_____________F
1_username____status____before____after____total changes___total same.
2_AAAA________A_________A________A__________0____________1
3_AAAA________A_________A________A__________0____________1
4_AAAA________A_________A________A__________0____________1
5_AAAA________A_________A________A__________0____________1
6_AAAA________A_________A________A__________0____________1
7_AAAA________A_________A________A__________0____________1

I have total changes so that it shows 1 when before isnt the same a
after.
Total same is when before and after are the same.

What I need to do now is come up with a way so that I can total change
and sames for the different usernames by searching for the username.

I have this code

C2 (This is cell of username) K2 (input of username to search)

=IF(AND(K2=C2;F2="1");"1";"0")

So it will put a 1 when the username matches and also total same is 1.

But this only works for row 2.

Is there a way I could use the same principle but reference A1-A7 fo
the username and total the 1s in F1-F7.

So above would return 6 because AAAA has 6 sames?

Thanks

Best to use sumif() statement

= sumif(A1:A7, K2 (or whever search username is), F1:F7)

this will sum the values in F for which the corrseponding values in
match K