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 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:C100)
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" <[email protected]> wrote in message
news:[email protected]...
> 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:C100)
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" <[email protected]> wrote in message
news:[email protected]...
> 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$65536)
>
> 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:C100)
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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$65536)
>
> 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:C100)
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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$65536)
>
> 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:C100)
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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$65536)
>
> 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:C100)
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:C100)
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" <[email protected]> wrote in message
news:[email protected]...
> 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:C100)
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" <[email protected]> wrote in message
news:[email protected]...
> 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)
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
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$65536)
>
> 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$65536)
>
> 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$65536)
>
> 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$65536)
>
> 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)
=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" <[email protected]> wrote in message
news:[email protected]...
> SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>
> 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)
=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" <[email protected]> wrote in message
news:[email protected]...
> SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> > >
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> > >
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> > >
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> > >
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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$65536)
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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>>>
>>>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. 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$65536)
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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>>>
>>>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. 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$65536)
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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>>>
>>>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. 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$65536)
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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>>>
>>>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.
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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
> >
> > 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)
>
>
>
=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" <[email protected]> wrote in message
news:[email protected]...
> SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>
> 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)
=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" <[email protected]> wrote in message
news:[email protected]...
> SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
>
> 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)
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
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)
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
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)
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65536)
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)
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)
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)
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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks