I need alternative formula for SUMIFS in Excel 2003

[Jerome]

I am using the SUMIFS formula in Excel 2007, but Excel 2003 doesn't
recognize this and I need to send the file to people using Excel 2003. I
tried to use SUMIF, but I have 3 criteria to search for.
Does anybody know of a similar formula that works for Excel 2003?

Thanks!

 

[Fred Smith]

The 2003 version of SUMIFS is SUMPRODUCT.

Scan the archives or post back if you need more information.

Regards,
Fred.

 

[Shane Devenshire]

Hi Jerome,

You need to use an array function for example if you want to sum all the
sales between 1/1/2008 and 12/31/2008 with the dates in column B1:B100 and
the sales number in C1:C100 your formula would be:

=SUM((B1:B100>=DATE(2008,1,1))*(B1:B100<=DATE(2008,12,31))*C1:C100)

this would be entered by press Shift Ctrl Enter instead of Enter.
Alternatively:

=SUMPRODUCT((B1:B100>=DATE(2008,1,1))*(B1:B100<=DATE(2008,12,31))*C1:C100)

can be used without array entry.

To add a third criteria just stick another *(.....) into the formula, for
example:

=SUMPRODUCT((B1:B100>=DATE(2008,1,1))*(B1:B100<=DATE(2008,12,31))*(D1100="Acct")*C1:C100)

Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
search for life beyond earth.

 

[Jerome]

Hello Shane,
Thank you for your answer, but I have difficulty to get it to work in my
database. I'll try to explain my problem.
I work with Excel 2003 and I have a database like this:

columnA columnB columnC columnD
FRUIT COLOR LOCATION QTY
apple yellow A 50
apple yellow B 25
apple yellow A 30
apple red A 40
apple red A 10
apple green C 45
apple green C 10
cherry red A 25
cherry red B 30
cherry orange B 20

Question: How do I use SUM or SUMPRODUCT or SUMIF to calculate how many
yellow apples I have in location A? Is there maybe another function I need
to use?

Thanks.