How to total data on one sheet to another

[redmars]

I have a database that have connected using the MySQL plugin to query pull the MySQL information in to excel and just need the totals of the information.

My excel is 2007, and the below is the information, just dont know how to do the formula for manipulating data.

Column D has the user id and column C has the hours, the user id can show up multiple times in the column D.

Looking to count the number of hours for the user1.
SUM = on sheet2 if column D has user1 copy and add up the number(hours) of column C.

Next count how many time user id was listed.
SUM = on sheet2 count how many time user1 name shows up in column D.

Any help is the right direction would be appreciated.

-RM

 

[VoG]

Try

=SUMIF(Sheet1!D:D,"User 1",Sheet1!C:C)

=COUNTIF(Sheet1!D:D,"User 1")

 

[redmars]

VoG,

Thank you very much. Something I didn't know before don't put a - in any part of the name of the sheet, causes you to band head on keyboard.

-RM

 

[redmars]

VoG

Another question about the counting if I wanted to change how the counting was done.

=COUNTIF(Sheet1!D:D,"User1")

=COUNTIF(Sheet1!D:D,"User1")+COUNTIF(Sheet1!B:B,"NEW")

Count the user1 in D:D only if B:B has the following status: NEW, ASSIGNED, REOPENED, VERIFIED or can it be as simple say not equal to CLOSED.

 

[VoG]

Try

=SUMPRODUCT(--(Sheet1!D1:D100="User 1"),--(Sheet1!B1:B100<>"CLOSED"))

 

[redmars]

That works but now, but I figured I need it be excluding CLOSED and RESOLVED.

=SUMPRODUCT(--(Sheet1!D1:D100="User 1"),--(Sheet1!B1:B100<>"CLOSED"),--(Sheet1!B1:B100<>"RESOLVED"))

I tried that but tell me wrong value, is their a way to do something like or in the statement?

-RM

 

[VoG]

Try

=SUMPRODUCT(--(Sheet1!D1:D100="User 1"),--(ISNUMBER(MATCH(Sheet1!B1:B100,{"NEW","ASSIGNED","REOPENED","VERIFIED"},0))))