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

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

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

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

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

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

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

Thanks for that, Biff.

Govind.

Biff wrote:

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(Ra w2!$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

When I sperate the data by date into thereown worsheet the formula below
calucaltes the totals by tech by month.

=SUMIF('2004-JAN_05'!$C$2:$C$65536,$A18,'2004-JAN_05'!J$2:$J$65536)

Where
2005_Jan_05 is Worksheet name for Jan
$A18 is the employee ID

All I am looking to do is calculate this without having to seperate the
dates into seperate worksheets.

"Biff" wrote:

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

Hi!

The formula below is not using a date(s) as one of the criteria.

All I can tell you is that there is nothing wrong with the syntax of the
formula presented earlier in this thread. Post the *EXACT* formula that you
tried and returns zero.

Biff

"Curtis" wrote in message
...
When I sperate the data by date into thereown worsheet the formula below
calucaltes the totals by tech by month.

=SUMIF('2004-JAN_05'!$C$2:$C$65536,$A18,'2004-JAN_05'!J$2:$J$65536)

Where
2005_Jan_05 is Worksheet name for Jan
$A18 is the employee ID

All I am looking to do is calculate this without having to seperate the
dates into seperate worksheets.

"Biff" wrote:

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

you are right. The formula below does not uses dates as on of the criteria.
It is not necessary as I have sperated the dates by month in their own
worksheet.

I was hoping to not have to do this manual seperation and just leave
everything in one sheet.

I will keep trying

Thanks

"Biff" wrote:

Hi!

The formula below is not using a date(s) as one of the criteria.

All I can tell you is that there is nothing wrong with the syntax of the
formula presented earlier in this thread. Post the *EXACT* formula that you
tried and returns zero.

Biff

"Curtis" wrote in message
...
When I sperate the data by date into thereown worsheet the formula below
calucaltes the totals by tech by month.

=SUMIF('2004-JAN_05'!$C$2:$C$65536,$A18,'2004-JAN_05'!J$2:$J$65536)

Where
2005_Jan_05 is Worksheet name for Jan
$A18 is the employee ID

All I am looking to do is calculate this without having to seperate the
dates into seperate worksheets.

"Biff" wrote:

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