# Using SUMPRODUCT (no SUMIFS) to summarize mapped data

1. ## Using SUMPRODUCT (no SUMIFS) to summarize mapped data

Hi all,

Please see my attached image to go along with my question. I'm trying to accomplish this without SUMIFs, as they require another workbook to be open. I am aware of how simply this could be done with SUMIFs, but I would like to do it using SUMPRODUCT or some other method.

I have a set of 1) Available Information in one workbook, which lists sales of products by years.
I have a 2) Mapping of products to product types.
I would like to produce a 3) Final Product that sums up the total sales of product types for each given year.

Is there some way for me to accomplish this using SUMPRODUCTS or another way? I've been racking my brain for hours.

SUMPRODUCT.PNG

Thanks so much!

3. ## Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

I have fixed this.

4. ## Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

In C28

=SUMPRODUCT((\$B\$4:\$B\$12={"A","B","C"})*(INDEX(\$C\$4:\$G\$12,,MATCH(C\$27,\$C\$3:\$G\$3,0))))

Copy across

Change "A","B","C" to appropriate group for Beta & gamma

5. ## Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

Thank you, however this is unsuitable, as in reality there are hundreds of products.

6. ## Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

what is the sumif formula that doesn't work? it should be able to be changed into a sumproduct

7. ## Re: Using SUMPRODUCT (no SUMIFS) to summarize mapped data

Try

=SUMPRODUCT((\$B\$4:\$B\$12=\$B\$16:\$B\$24)*(\$C\$16:\$C\$24=\$B28)*(INDEX(\$C\$4:\$G\$12,,MATCH(C\$27,\$C\$3:\$G\$3,0))))

There are currently 1 users browsing this thread. (0 members and 1 guests)