EXCEL 2000 SUMIFS alternative

[atlong00]

Hi Everyone, I was using openoffice calc and having problems so I decided to use the version of EXEL 2000 I have. Well it does not support SUMIFS. I have been trying unsuccessfully to setup my multiple critera formula using SUMIF and SUMPRODUT, but no success.

Below is the code i was using in openoffice can someone help with my problem?

=SUMIFS(Transactions!$I$5:$I$1197,Transactions!$B$5:$B$1197,"^02.*",Transactions!$F$5:$F$1197,Budget!$A91)

This sums the Deposit column I after checking the current month in column B to see if the first two characters are 02 and if the text in Budget!A91 matches any of the text in the category column F.

Thanks in advance for any help

 

[FDibbins]

Hi, welcome to the board

maybe this (based only on the formula you provided)...
=SUMproduct((Transactions!$I$5:$I$1197),--(left(Transactions!$B$5:$B$1197,2)=02),--(Transactions!$F$5:$F$1197,Budget!$A91))
untested

 

[atlong00]

Hi Thanks for the quick response, the code you sent has the same problems I was having. The first array testing the date gets 0 as the result. I have the date column formated as a date mm/dd/yyyy. On the last array I am getting a value error. Any other suggestions would be appreciated.

Thanks for your help

 

[FDibbins]

OK I figured, from what you said, the date was text, try this if you are using real dates...
=SUMproduct((Transactions!$I$5:$I$1197),--(month(Transactions!$B$5:$B$1197,2)=2),--(Transactions!$F$5:$F$1197,Budget!$A91))