SumIf

[Guest]

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

 

[Govind]

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.

 

[Guest]

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]

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.

 

[Guest]

Not in columns B, C, or J, but there is $0.00 values in column J.
Yes Header is not in Range

 

[Govind]

Hi,

Not sure why it doesnt work. IF you want you can send the spreadsheet to
my email id.

Govind.

 

[Biff]

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

 

[Govind]

Thanks for that, Biff.

Govind.

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

 

[Guest]

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]

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

 

[Guest]

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