Help for a formula using two conditions

[harwookf]

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.

 

[Frank Kabel]

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

 

[Biff]

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

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.

.

 

[harwookf]

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.

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

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.

 

[harwookf]

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.

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

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.

.

 

[Frank Kabel]

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:E100)

--
Regards
Frank Kabel
Frankfurt, Germany

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.

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

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.

.

 

[Biff]

Hi!

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

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

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

Biff

-----Original Message-----
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.

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" <[email protected]>

schrieb
im Newsbeitrag

news:3A40AC9B-77D4-4DC8-90E4- (e-mail address removed)...
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.

.

.

 

[harwookf]

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.

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:E100)

--
Regards
Frank Kabel
Frankfurt, Germany

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.

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

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.

.