Excel Dynamic Chart #8: SUMPRODUCT function (Excel 2003 substitute for SUMIFS)

Hello all

I have a SUMIFS formula which works in 2007 however I need backward compatibility with excel 2003 as I don't want to have to make 2 versions of the workbook. I've tried entering a sumproduct alternative after researching these forums however it returns the #VALUE error.

the formula that works in 2007 is:

Code:

=SUM(SUMIFS(KVS!C:C,KVS!A:A,Control!J40,KVS!B:B,{"0600","0615","0630","0645"}))

the sumproduct alternative which doesn't work :

Code:

=SUMPRODUCT(--(KVS!A1:A1345="Control!J40"),--(KVS!B1:B1345={"0600","0615","0630","0645"}),KVS!C1:C1345)

any help at all would be greatly appreciated

Adam

Hi,

I have the following table:

Sheet3

* B C D E F 2 Dept Month Region Color Amount 3 M1 Jan West Green 800 4 M2 Feb South Brown 500 5 M3 Mar East Yellow 300 6 M4 Apr North White 200 7 M1 Jan West Green 300 8 M2 Feb South Brown 500 9 M3 Mar East Yellow 300 10 M4 Apr North White 200 11 * * * * * 12 800 * * * * 13 0 * * * *
Spreadsheet Formulas Cell Formula B12 =SUMIFS(F3:F10,B3:B10,B3,C3:C10,C3,D3:D10,D3,E3:E10,E3,F3:F10,">500") B13 =SUMPRODUCT((B3:B10="M1")*(C3:C10="Jan")*(D3:D10="West")*(E3:E10="Green"),F3:F10>"500")

Excel tables to the web >> Excel Jeanie HTML 4

In cell B13, I would like to replicate the same sumifs formula in cell B12. However, I am getting 0 in cell B13 instead of 800.

Aprreciate assistance to tweak this sumproduct formula to mirror the sumif to get a desired result of 800.

After seaching the Interweb for an answer to no avail, I have come here for your help. Here is my question:

In Excel 2007 and newer, there is a function called COUNTIFS that allows you to compare the values of two separate columns and only count it if the values of both columns equal the values indicated in the formula.

Here at work, I have Excel 2003, which does not have the COUNTIFS option. Is there anything else I can use to compare the values of two different columns and only count it if both columns match the variables indicated in the formula?

As always, thanks in advance for everyone's help!

Scott

Dear All,

I have formula in Excel 2010

=SUMIFS($DK6:$HW6,$DK$3:$HW$3,"Quantity (unit)",$DK$2:$HW$2,">="&$B$1,$DK$2:$HW$2,"

I am using the following function in 2007
=IF(E237="","",SUMIFS('General Ledger'!$G$6:$G$1048576,'General Ledger'!$A$6:$A$1048576,E237,'General Ledger'!$F$6:$F$1048576,"Current Period Change"))

but when saving the file into 2003 i receive incompatibility msg concerning the number of rows and sumifs function.
Okay I reduced the number of rows but I am struggling to get around other problem. I am wondering if you could help me creating UDF with the same format of sumifs i.e.(sum_range,criteria_range1,criteria1,...). or maybe give me some other solution.
thanks

excel 2007 doesn't work for apple so I have to convert this code into an excel 2003 format

=SUMIFS(time!$F:$F,time!$H:$H,day!C$1,time!$B:$B,day!$A3)

this is for a spreadsheet that i will need to use all the time since it keeps track of how i spend my time. opening up the pc side on my apple computer can't be done all the time.

Hi,

How would I convert the following formula into Excel 2003?

=SUMIFS($Q$7:$Q$5000,$O$7:$O$5000,"Cat",$P$7:$P$5000,"Dog")

Thanks,
John

On Summary Tab A I have text in Column A, Text or # in Column B, and a Value in column C. On Detail Tab B I have Text in Column A, Text or # in Column B, and a Value in Column C. The Detail Tab B has repeated instances of data with the same Column A & B criteria.

On Summary Tab A, I need a Column D that contains the Sum of Column C Values from Detail Tab B, when Summary Tab A's criteria in Columns A AND B are present in Detail Tab B's Columns A AND B.

The outcome will tell me if, given criteria in Column A & Column B, the summary value in Column C of Summary Tab A equals the sum of values in Column C of detail Tab B, where criteria in Columns A & B match. The reason I want the actual sumation in new Column D is because when a variance exists, I will have Column C-Column D in new Column E.

Dear All,

I've created a spreadsheet with numerous SUMIFS formulas (1,475 to be exact)

When I rolled it out to the rest of the company it was soon discovered that they had Excel 2003 not Excel 2007 like myself. Consequently it doesn't work.

My desire to change 1,475 cells to a sumproduct formula is non existent to say the least.

Does anyone know of a way to create a SUMIFS function in Excel 2003 with VBA code to get around this?

thanks

I have changed my work from Excel 2007 to Excel 2003. I want to sum using multiple conditions but SUMIFS is not available in 2003. I tried using the AND function but it wouldn't work. Can someone please help me? below is what I used:

=SUMIF(U2:Z2,AND("=>3","