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!
|
Answer
Answer
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↵.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer
... 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))

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated September 7, 2020 Views 6,580 Applies to: