Sum of a range where multiple column criteria and row criteria

[Emsav]

Hi guys,

I am looking to sum a range of cells whereby the criteria is e.g. shop AND manager AND sep-18 - to populate the tables on the right.


Screen Shot 2018-05-21 at 17.36.21.png


I would appreciate some help, thanks!

[JohnTopley]

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Rev12]

What have you tried and what was the result??

Regards,
Rev12

[Emsav]

Here is the sample notebook with a before and after sheet.

The after sheet displays what data I want it to show as well as the formulas I have tried which all come back with '#VALUE"

=SUMIFS(Before!C2:J4,Before!A1:A4,"Paper",Before!B1:B4,"Magazine",Before!C1:J1,"Sep18")

=SUMPRODUCT(Before!A1:A4="Paper")*(Before!B1:B4="Magazine")*(Before!C1:J1="Sep-18")*(Before!C2:J4)

=SUM(IF(Before!C1:J1="Sep-18",IF(Before!A1:A4="Paper",IF(Before!B1:B4="Magazine",Before!C2:J4))))

[JohnTopley]

Removed merged cells in before

in After

in B2

=SUMPRODUCT((Before!$C$2:$J$4)*(Before!$C$1:$J$1=After!B$1)*(Before!$B$2:$B$4=After!$A$1)*(Before!$A$2:$A$4=After!$A2))

copy across and down

[Rev12]

May be it would help please try as attached Excel

[Emsav]

Thank you so much!!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.