I need alternative formula for SUMIFS in Excel 2003

  • Thread starter Thread starter Jerome
  • Start date Start date
J

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!
 
The 2003 version of SUMIFS is SUMPRODUCT.

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

Regards,
Fred.
 
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))*(D1:D100="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.
 
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.
 
Back
Top