Sumifs 2007 to 2003 Conversion Help Please

[voddy]

Hi all,

First post here....i've had a look through various sites & help topics but am still stuck (& tearing my hair out!)....I have been practising with a basic Sumifs formula in Excel 2007, & when I have gone to use it at my work place work in Excel 2003 it is not working, & after a little investigation I have found out that I need to use the Sumproduct function in Excel 2003.

I am really struggling!! I've tried for over an hour to convert the formula & i'm having no luck at all, & i have a piece of work that I need to get finished ASAP

I would be hugely grateful if anyone could help me convert the following formulas from Excel 2007 Sumifs into Excel 2003 Sumproducts;

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,"Life")

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,"Life",Sheet1!C:C,">=1/1/2011",Sheet1!C:C,"<=31/1/2011")

Many thanks in advance for any help you can give me
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=64></TD></TR></TBODY></TABLE>

 

[Marcelo Branco]

Hi,

Welcome to the forum!

Maybe

First formula
=SUMPRODUCT(--(Sheet1!$A$2:$A$100=A2),--(Sheet1!$B$2:$B$100="Life"),Sheet1!$D$2:$D$100)

Adjust the ranges accordingly (avoid references to the entire column like A:A. I dont know if this is even allowed in 2003 and could cause a bad performance in the case of SUMPRODUCT)

Same idea for the second formula.

Take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

HTH

M.

 

[T. Valko]

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,"Life",Sheet1!C:C,">=1/1/2011",Sheet1!C:C,"<=31/1/2011")

Here's one way to write the other formula.

=SUMPRODUCT(--(Sheet1!A1:A100=A2),--(Sheet1!B1:B100="Life"),--(TEXT(Sheet1!C1:C100,"mmmyyyy")="Jan2011"),Sheet1!D1:D100)

Note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007 or later.

 

[voddy]

Thanks to you both, your suggestions worked perfectly....you've both saved me alot of time & headaches! Much appreciated

 

[T. Valko]

Thanks to you both, your suggestions worked perfectly....you've both saved me alot of time & headaches! Much appreciated

You're welcome. Thanks for the feedback!