Thread: sum ifs + index match Thanks: 0 Likes: 0

1. sum ifs + index match

Hi, I'm trying to accomplish the following results:

 January Publix 2019 2018 2017 Fruits Pear 6 10 2 Mango - 1 3 Banana 4 5 7

 Year Fruit Store January February March 2018 Pear Publix 10 5 3 2018 Mango Publix 1 20 2 2019 Banana Whole Foods 10 20 30 2019 Pear Fresh Market 1 2 3 2019 Pear Publix 2 1 1 2019 Pear Publix 2 1 1 2019 Pear Publix 2 1 1 2017 Mango Whole Foods 1 10 2 2018 Banana Publix 5 5 10 2017 Pear Publix 2 3 4 2017 Mango Publix 3 3 4 2017 Banana Publix 4 3 4 2017 Banana Publix 2 1 1 2019 Banana Publix 2 1 1 2019 Banana Publix 2 1 1

which combination of formulas do I need?

2. Re: sum ifs + index match

Here is one way with SUMPRODUCT. Copy formula down and across as needed.
You may also want to look at a pivot table.

 A B C D E F 1 January 2 Publix 3 2019 2018 2017 4 Fruits 5 Pear 6 10 2 6 Mango 0 1 3 7 Banana 4 5 6 8 9 10 11 Year Fruit Store January February March 12 2018 Pear Publix 10 5 3 13 2018 Mango Publix 1 20 2 14 2019 Banana Whole Foods 10 20 30 15 2019 Pear Fresh Market 1 2 3 16 2019 Pear Publix 2 1 1 17 2019 Pear Publix 2 1 1 18 2019 Pear Publix 2 1 1 19 2017 Mango Whole Foods 1 10 2 20 2018 Banana Publix 5 5 10 21 2017 Pear Publix 2 3 4 22 2017 Mango Publix 3 3 4 23 2017 Banana Publix 4 3 4 24 2017 Banana Publix 2 1 1 25 2019 Banana Publix 2 1 1 26 2019 Banana Publix 2 1 1

 Cell Formula B5 =SUMPRODUCT((\$D\$11:\$F\$11=\$A\$1)*(\$A\$12:\$A\$26=B\$3)*(\$B\$12:\$B\$26=\$A5)*(\$C\$12:\$C\$26=\$B\$2)*(\$D\$12:\$F\$26))

Excel tables to the web >> Excel Jeanie HTML 4

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•