Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
SumIf
I need to calculate the sum of numbers that are dependent on 2 conditions. One
Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#2
|
|||
|
|||
Hi,
Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J1:J1 00)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#3
|
|||
|
|||
It returns a value of 0 no matter what month is selected (=2, =3, etc).. Same
with Employee # used formula =SUMPRODUCT((Raw2!$C$2:$C$1000=2386)*(MONTH(Raw2!$ B$2:$B$1000)=4)*(Raw2!$J$2:$J$1000)) "Govind" wrote: Hi, Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J1:J1 00)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#4
|
|||
|
|||
Hi,
Are there any blank rows in your range of 2 to 1000? If yes, then the formula might return an error. also I presume the header row is kept out of your range. Govind. Curtis wrote: It returns a value of 0 no matter what month is selected (=2, =3, etc).. Same with Employee # used formula =SUMPRODUCT((Raw2!$C$2:$C$1000=2386)*(MONTH(Raw2!$ B$2:$B$1000)=4)*(Raw2!$J$2:$J$1000)) "Govind" wrote: Hi, Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J1: J100)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#5
|
|||
|
|||
Not in columns B, C, or J, but there is $0.00 values in column J.
Yes Header is not in Range "Govind" wrote: Hi, Are there any blank rows in your range of 2 to 1000? If yes, then the formula might return an error. also I presume the header row is kept out of your range. Govind. Curtis wrote: It returns a value of 0 no matter what month is selected (=2, =3, etc).. Same with Employee # used formula =SUMPRODUCT((Raw2!$C$2:$C$1000=2386)*(MONTH(Raw2!$ B$2:$B$1000)=4)*(Raw2!$J$2:$J$1000)) "Govind" wrote: Hi, Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J1: J100)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#6
|
|||
|
|||
Hi,
Not sure why it doesnt work. IF you want you can send the spreadsheet to my email id. Govind. Curtis wrote: Not in columns B, C, or J, but there is $0.00 values in column J. Yes Header is not in Range "Govind" wrote: Hi, Are there any blank rows in your range of 2 to 1000? If yes, then the formula might return an error. also I presume the header row is kept out of your range. Govind. Curtis wrote: It returns a value of 0 no matter what month is selected (=2, =3, etc).. Same with Employee # used formula =SUMPRODUCT((Raw2!$C$2:$C$1000=2386)*(MONTH(Raw 2!$B$2:$B$1000)=4)*(Raw2!$J$2:$J$1000)) "Govind" wrote: Hi, Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J 1:J100)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) €“ Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
#7
|
|||
|
|||
Hi!
Blank rows won't cause a problem. Check that the dates in column B are true Excel dates and not just text entries that look like dates. True Excel dates are really numbers that are just formatted to look like dates. Also, check to make sure that the employee numbers in column C are also true numbers and not just text strings the look like numbers. You may as well check the values in column J for the same thing. There's nothing wrong with the formula so you have to look at the data. Biff "Curtis" wrote in message ... Not in columns B, C, or J, but there is $0.00 values in column J. Yes Header is not in Range "Govind" wrote: Hi, Are there any blank rows in your range of 2 to 1000? If yes, then the formula might return an error. also I presume the header row is kept out of your range. Govind. Curtis wrote: It returns a value of 0 no matter what month is selected (=2, =3, etc).. Same with Employee # used formula =SUMPRODUCT((Raw2!$C$2:$C$1000=2386)*(MONTH(Raw2!$ B$2:$B$1000)=4)*(Raw2!$J$2:$J$1000)) "Govind" wrote: Hi, Use =SUMPRODUCT((C:C100=EMPLOYEE NO.)*(MONTH(B1:B100)=MONTH YOU NEED)*(J1:J100)) where C1 to C100 is the range with employee no.s , B1 to B100 is the range with dates and J1 to J100 is the range to be summed up. Lets say you need data for employee no. 500 for the month 2 -February, use =SUMPRODUCT((C:C100=500)*(MONTH(B1:B100)=2)*(J1: J100)) Govind. Curtis wrote: I need to calculate the sum of numbers that are dependent on 2 conditions. One Condition is employee number (in column C) and the other condition is the month (in column B) - Column B contains dates listed by day. I would like to calculate the sum (listed in column J), by month for a specific employee. There will be multiple sheets within this workbook. One sheet will report the data as identified above and the others will contain the raw data (these worksheetes will be quite large). Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |