Substitution for SUMIFS function for MS Excel 2003 version

Good Day to all!

Do you have any suggestion for a substitution of the SUMIFS function for the MS Excel 2003 version.

Thanks!
|
i earlier tried to use a B:B, C:C, D:D for this function but it showed =!value.

SUMPRODUCT() doesn't skip over text values when adding numbers like a SUM() function will do so any text such as column header labels would have produced the #VALUE! errors. You can get around this with an array formula like,
=SUM(IF(Sheet1!B:B=\$E14,IF(Sheet1!C:C=\$F14,Sheet1!D:D)))
... which is finalized with Ctrl+Shift+Enter↵ rather than just Enter↵.

·

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

... and then i wanted the TOTAL # of students will be added having the COURSES AND MAJOR as reference.  This will be on another sheet.

A SUMPRODUCT() formula using the Course and Major as criteria should be sufficient.

The formula in D14 is,
=SUMPRODUCT((\$B\$5:\$B\$11=\$B14)*(\$C\$5:\$C\$11=\$C14)*(\$D\$5:\$D\$11))
... which can be filled down as necessary to accomadate the values in columns B and C. This style of formula will work just as well on another worksheet as long as the range references include the worksheet in the addresses. Something like,
=SUMPRODUCT((Sheet1!\$B\$5:\$B\$11=\$B14)*(Sheet1!\$C\$5:\$C\$11=\$C14)*(Sheet1!\$D\$5:\$D\$11))

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?