Calculate sum of numbers with conditions
 

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other is
the month (in a column listed with many days)

Curtis,

An array formula would work here. Could you please give a little bit more
detail about your data? You mention days. Does this mean the month is to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other is
the month (in a column listed with many days)

Try

=SUMPRODUCT((A1:A100=123)*(MONTH(B1:B100)=5),C1:C1 00)

123 = employee number
month = may (5th month)
c1:c100 is the range you want to sum if above 2 conditions are true on the
same row.

"Curtis" wrote in message
...
I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other
is
the month (in a column listed with many days)

SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit more
detail about your data? You mention days. Does this mean the month is to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other is
the month (in a column listed with many days)

In answering your question the amount the month is to be calculated from a
date----the answer is yes. each row in the sheet has a date assigned to it
which could represent any day of any month.

Hope this helps and thanks for your help

"Curtis" wrote:

SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit more
detail about your data? You mention days. Does this mean the month is to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other is
the month (in a column listed with many days)

DId really do it unless I am doing something wrong or did not give you enough
info.

SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well keeping in mind that each row in the sheet has a date assigned to it which could represent any day of any month of any year.




"Dave R." wrote:

Try

=SUMPRODUCT((A1:A100=123)*(MONTH(B1:B100)=5),C1:C1 00)

123 = employee number
month = may (5th month)
c1:c100 is the range you want to sum if above 2 conditions are true on the
same row.

"Curtis" wrote in message
...
I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other
is
the month (in a column listed with many days)

=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit
more
detail about your data? You mention days. Does this mean the month is
to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the
other is
the month (in a column listed with many days)

Doesn't work. However it could be my fault as I might not be relaying all the
information.

The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) ,
look at a specific tech number (column c), look at the speciic tech number (
column b) and then sum up the $$ for that tech for that month. The days are
listed in column c, and can include any date)

Hope that helps

ce


"Bob Phillips" wrote:

=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit
more
detail about your data? You mention days. Does this mean the month is
to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the
other is
the month (in a column listed with many days)

I noticed a typo

s/b The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) ,
look at a specific tech number (column c), look at the speciic month (
column b) and then sum up the $$ for that tech for that month. The days are
listed in column b, and can include any date)


"Curtis" wrote:

Doesn't work. However it could be my fault as I might not be relaying all the
information.

The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) ,
look at a specific tech number (column c), look at the speciic tech number (
column b) and then sum up the $$ for that tech for that month. The days are
listed in column c, and can include any date)

Hope that helps

ce


"Bob Phillips" wrote:

=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit
more
detail about your data? You mention days. Does this mean the month is
to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the
other is
the month (in a column listed with many days)

Why is this giving me an error? It doesn't make sense!!!!

SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw -
2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536)

Column c is employee number
Column b is date range
Column j is $$ range

"Bob Phillips" wrote:

=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:

Curtis,

An array formula would work here. Could you please give a little bit
more
detail about your data? You mention days. Does this mean the month is
to be
calculated from a date?

Thanks,
Matt

"Curtis" wrote:

I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the
other is
the month (in a column listed with many days)

1. Change Raw - 2nd to Raw2nd.
2. In B37 enter the first day date of a month/year of interest. For example:

1-Apr-05

Then invoke:

=SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536)

The range you apply the formula to is too big. If it must be that big,
consider switching to a SumIf formula for efficiency...

In K2 enter and copy down:

=C2&"#"&B2-DAY(B2)+1

Then invoke:

=SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd!$ J$2:$J$65536)

Curtis wrote:
Why is this giving me an error? It doesn't make sense!!!!

SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw -
2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536)

Column c is employee number
Column b is date range
Column j is $$ range

"Bob Phillips" wrote:


=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...

SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:


Curtis,

An array formula would work here. Could you please give a little bit

more

detail about your data? You mention days. Does this mean the month is

to be

calculated from a date?

Thanks,
Matt

"Curtis" wrote:


I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the

other is

the month (in a column listed with many days)




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

I used your second suggestion

=SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&1-Apr-5,Raw2!$J$2:$J$65536)

and it returns a value of 0 which is not correct.

Note: the formula =C2&"#"&B2-DAY(B2)+1 was entered in p2 and copied down.
The worksheet has other info.

Also the totals are being capture in a different sheet in the workbook

thanks



"Aladin Akyurek" wrote:

1. Change Raw - 2nd to Raw2nd.
2. In B37 enter the first day date of a month/year of interest. For example:

1-Apr-05

Then invoke:

=SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536)

The range you apply the formula to is too big. If it must be that big,
consider switching to a SumIf formula for efficiency...

In K2 enter and copy down:

=C2&"#"&B2-DAY(B2)+1

Then invoke:

=SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd!$ J$2:$J$65536)

Curtis wrote:
Why is this giving me an error? It doesn't make sense!!!!

SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw -
2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536)

Column c is employee number
Column b is date range
Column j is $$ range

"Bob Phillips" wrote:


=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...

SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:


Curtis,

An array formula would work here. Could you please give a little bit

more

detail about your data? You mention days. Does this mean the month is

to be

calculated from a date?

Thanks,
Matt

"Curtis" wrote:


I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the

other is

the month (in a column listed with many days)




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

The $A37&"#"&1-Apr-5 bit as such won't work.

Either put 1-Apr-05 in, say, B37 and invoke:

=SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&$B$37,Raw2!$J$2 :$J$65536)

or

express 1-Apr-05 directly as DATE(2005,4,1) and invoke:

=SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&DATE(2005,4,1), Raw2!$J$2:$J$65536)

Curtis wrote:
I used your second suggestion

=SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&1-Apr-5,Raw2!$J$2:$J$65536)

and it returns a value of 0 which is not correct.

Note: the formula =C2&"#"&B2-DAY(B2)+1 was entered in p2 and copied down.
The worksheet has other info.

Also the totals are being capture in a different sheet in the workbook

thanks



"Aladin Akyurek" wrote:


1. Change Raw - 2nd to Raw2nd.
2. In B37 enter the first day date of a month/year of interest. For example:

1-Apr-05

Then invoke:

=SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536)

The range you apply the formula to is too big. If it must be that big,
consider switching to a SumIf formula for efficiency...

In K2 enter and copy down:

=C2&"#"&B2-DAY(B2)+1

Then invoke:

=SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd !$J$2:$J$65536)

Curtis wrote:

Why is this giving me an error? It doesn't make sense!!!!

SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw -
2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536)

Column c is employee number
Column b is date range
Column j is $$ range

"Bob Phillips" wrote:



=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curtis" wrote in message
...


SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)

this sums up the $$ by employee number. I need to add a formula that pulls
the $$ by the month as well .

"Matt Lunn" wrote:



Curtis,

An array formula would work here. Could you please give a little bit

more


detail about your data? You mention days. Does this mean the month is

to be


calculated from a date?

Thanks,
Matt

"Curtis" wrote:



I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the

other is


the month (in a column listed with many days)



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.