Help for a formula using two conditions
 

Hi. Can anybody help me with a formula based on two conditions.

I have a spreadsheet with various columns (example shown below).

Month Customer Contract Type Contract Value Yr Value
Apr 04 xxx New £10,000 £2,000
Apr 04 xxx Upgrade £2,000 £500
May 04 xxx New £15,000 £5,000

I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr 04'.
In the example above, I expect to get £2,000 for Apr 04 and £5,000 for May 04.
This could be more complicated as the spreadsheet I want to put the formula
in is different from where the information is.

I have tried various formula with no success. Below is an example of one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr 04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue 04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

Hi
if the month value is not a date but a text value try:
=SUMPRODUCT(--(A1:A100="Apr 04"),--(C1:C100="New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two conditions.

I have a spreadsheet with various columns (example shown below).

Month Customer Contract Type Contract Value Yr Value
Apr 04 xxx New £10,000 £2,000
Apr 04 xxx Upgrade £2,000 £500
May 04 xxx New £15,000 £5,000

I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr
04'.
In the example above, I expect to get £2,000 for Apr 04 and £5,000
for May 04.
This could be more complicated as the spreadsheet I want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is an example of
one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

Hi!

His formula should have worked if the date value was a=20
text string. He has it quoted in the formula.

Try this:

=3DSUMPRODUCT(--(TEXT(A1:A4,"mmm yy")=3D"apr 04"),--
(B1:B4=3D"new"),C1:C4)

Biff

-----Original Message-----
Hi
if the month value is not a date but a text value try:
=3DSUMPRODUCT(--(A1:A100=3D"Apr 04"),--
(C1:C100=3D"New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb=20
im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two=20
conditions.

I have a spreadsheet with various columns (example=20
shown below).

Month Customer Contract Type Contract Value Yr=20
Value
Apr 04 xxx New =C2=A310,000 =20
=C2=A32,000
Apr 04 xxx Upgrade =C2=A32,000 =20
=C2=A3500
May 04 xxx New =C2=A315,000 =20
=C2=A35,000

I need to be able to total the 'Yr Value' for 'New'=20
contracts in 'Apr
04'.
In the example above, I expect to get =C2=A32,000 for Apr=20
04 and =C2=A35,000
for May 04.
This could be more complicated as the spreadsheet I=20
want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is=20
an example of
one
that I have tried.

=3DSUM(IF(('[New Revenue 04_05.xls]New Revenue'!
$A$2:$A$300=3D"Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300=3D"New"),'[New=20
Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

.

How do I link it to the spreadsheet with the data. The one I am putting the
formula in is a 'Comparisons' as it pulls data from different sources, but
the data is in 'New Revenue', etc
Thanks.

"Frank Kabel" wrote:

Hi
if the month value is not a date but a text value try:
=SUMPRODUCT(--(A1:A100="Apr 04"),--(C1:C100="New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two conditions.

I have a spreadsheet with various columns (example shown below).

Month Customer Contract Type Contract Value Yr Value
Apr 04 xxx New £10,000 £2,000
Apr 04 xxx Upgrade £2,000 £500
May 04 xxx New £15,000 £5,000

I need to be able to total the 'Yr Value' for 'New' contracts in 'Apr
04'.
In the example above, I expect to get £2,000 for Apr 04 and £5,000
for May 04.
This could be more complicated as the spreadsheet I want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is an example of
one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!$A$2:$A$300="Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

What if the date value is a date but shown as mmm yy? I still don't
understand how this formula knows which spreadsheet to find the data from.

"Biff" wrote:

Hi!

His formula should have worked if the date value was a
text string. He has it quoted in the formula.

Try this:

=SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),--
(B1:B4="new"),C1:C4)

Biff

-----Original Message-----
Hi
if the month value is not a date but a text value try:
=SUMPRODUCT(--(A1:A100="Apr 04"),--
(C1:C100="New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb
im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two
conditions.

I have a spreadsheet with various columns (example
shown below).

Month Customer Contract Type Contract Value Yr
Value
Apr 04 xxx New £10,000
£2,000
Apr 04 xxx Upgrade £2,000
£500
May 04 xxx New £15,000
£5,000

I need to be able to total the 'Yr Value' for 'New'
contracts in 'Apr
04'.
In the example above, I expect to get £2,000 for Apr
04 and £5,000
for May 04.
This could be more complicated as the spreadsheet I
want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is
an example of
one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!
$A$2:$A$300="Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New
Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

.

Hi
first use:
=SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2004),--(C1:C100="New
"),E1:E100)

If you need to do this on a different worksheet you have to change the
range references accordingly. e.g.
=SUMPRODUCT(--(MONTH('other_sheet'!A1:A100)=4),--(YEAR('other_sheet'!A1
:A100)=2004),--('other_sheet'!C1:C100="New"),'other_sheet'!E1:E10 0)

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb im Newsbeitrag
...
What if the date value is a date but shown as mmm yy? I still don't
understand how this formula knows which spreadsheet to find the data
from.

"Biff" wrote:

Hi!

His formula should have worked if the date value was a
text string. He has it quoted in the formula.

Try this:

=SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),--
(B1:B4="new"),C1:C4)

Biff

-----Original Message-----
Hi
if the month value is not a date but a text value try:
=SUMPRODUCT(--(A1:A100="Apr 04"),--
(C1:C100="New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb
im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two
conditions.

I have a spreadsheet with various columns (example
shown below).

Month Customer Contract Type Contract Value Yr
Value
Apr 04 xxx New £10,000
£2,000
Apr 04 xxx Upgrade £2,000
£500
May 04 xxx New £15,000
£5,000

I need to be able to total the 'Yr Value' for 'New'
contracts in 'Apr
04'.
In the example above, I expect to get £2,000 for Apr
04 and £5,000
for May 04.
This could be more complicated as the spreadsheet I
want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is
an example of
one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!
$A$2:$A$300="Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New
Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

.

Hi!

Use Frank's second formula if the dates are true dates.

My formula just converts true dates into text strings. It=20
will work but Franks is better. His are always better <g

The best way to get your ranges is to have the other=20
workbook open. As you enter the formula use the mouse to=20
select the ranges from the other workbook. That way, Excel=20
will automatically enter the full path saving you the=20
trouble of typing it in. Some peoples paths can be=20
ridiculosly long!

Biff

-----Original Message-----
What if the date value is a date but shown as mmm yy? I=20
still don't=20
understand how this formula knows which spreadsheet to=20
find the data from.

"Biff" wrote:

Hi!
=20
His formula should have worked if the date value was a=20
text string. He has it quoted in the formula.
=20
Try this:
=20
=3DSUMPRODUCT(--(TEXT(A1:A4,"mmm yy")=3D"apr 04"),--
(B1:B4=3D"new"),C1:C4)
=20
Biff
=20
-----Original Message-----
Hi
if the month value is not a date but a text value try:
=3DSUMPRODUCT(--(A1:A100=3D"Apr 04"),--
(C1:C100=3D"New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" =20
schrieb=20
im Newsbeitrag
news:3A40AC9B-77D4-4DC8-90E4-
...
Hi. Can anybody help me with a formula based on two=20
conditions.

I have a spreadsheet with various columns (example=20
shown below).

Month Customer Contract Type Contract Value =20
Yr=20
Value
Apr 04 xxx New =20
=C3,=C2=A310,000 =20
=C3,=C2=A32,000
Apr 04 xxx Upgrade =20
=C3,=C2=A32,000 =20
=C3,=C2=A3500
May 04 xxx New =20
=C3,=C2=A315,000 =20
=C3,=C2=A35,000

I need to be able to total the 'Yr Value' for 'New'=20
contracts in 'Apr
04'.
In the example above, I expect to get =C3,=C2=A32,000 for=20
Apr=20
04 and =C3,=C2=A35,000
for May 04.
This could be more complicated as the spreadsheet I=20
want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below=20
is=20
an example of
one
that I have tried.

=3DSUM(IF(('[New Revenue 04_05.xls]New Revenue'!
$A$2:$A$300=3D"Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!
$D$2:$D$300=3D"New"),'[New=20
Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

.

=20
.

Thanks very much to you both. I have now got the spreadsheet working and
formulating correctly. Had to change my date to text, but it works and I'm
very happy. Just wished I posted the problem earlier as it has been puzzling
me for days!!

Thanks again.


"Frank Kabel" wrote:

Hi
first use:
=SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2004),--(C1:C100="New
"),E1:E100)

If you need to do this on a different worksheet you have to change the
range references accordingly. e.g.
=SUMPRODUCT(--(MONTH('other_sheet'!A1:A100)=4),--(YEAR('other_sheet'!A1
:A100)=2004),--('other_sheet'!C1:C100="New"),'other_sheet'!E1:E10 0)

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb im Newsbeitrag
...
What if the date value is a date but shown as mmm yy? I still don't
understand how this formula knows which spreadsheet to find the data
from.

"Biff" wrote:

Hi!

His formula should have worked if the date value was a
text string. He has it quoted in the formula.

Try this:

=SUMPRODUCT(--(TEXT(A1:A4,"mmm yy")="apr 04"),--
(B1:B4="new"),C1:C4)

Biff

-----Original Message-----
Hi
if the month value is not a date but a text value try:
=SUMPRODUCT(--(A1:A100="Apr 04"),--
(C1:C100="New"),E1:E100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"harwookf" schrieb
im Newsbeitrag
...
Hi. Can anybody help me with a formula based on two
conditions.

I have a spreadsheet with various columns (example
shown below).

Month Customer Contract Type Contract Value Yr
Value
Apr 04 xxx New £10,000
£2,000
Apr 04 xxx Upgrade £2,000
£500
May 04 xxx New £15,000
£5,000

I need to be able to total the 'Yr Value' for 'New'
contracts in 'Apr
04'.
In the example above, I expect to get £2,000 for Apr
04 and £5,000
for May 04.
This could be more complicated as the spreadsheet I
want to put the
formula
in is different from where the information is.

I have tried various formula with no success. Below is
an example of
one
that I have tried.

=SUM(IF(('[New Revenue 04_05.xls]New Revenue'!
$A$2:$A$300="Apr
04")*('[New
Revenue 04_05.xls]New Revenue'!$D$2:$D$300="New"),'[New
Revenue
04_05.xls]New
Revenue'!$K$2:$K$300))

Thanks.

.