Help for a formula using two conditions

H

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.
 
B

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.

.
 
H

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.

Frank Kabel said:
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 said:
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.
 
H

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.

Biff said:
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.

.
 
F

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

harwookf said:
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 said:
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.

.
 
B

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.

Biff said:
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.

.
.
 
H

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.


Frank Kabel said:
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

harwookf said:
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 said:
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.

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top