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