Sumifs or sumproduct with multiple criteria horizontal and vertical

amc1528

New Member
Joined
Aug 28, 2015
Messages
19
I'm trying to do a sumifs or sumproduct with multiple criteria. I have lookups both vertically and horizontally and want the sum of the number that matches all criteria. If I needed no sum, I can do an index match, match, match, match. If I only had horizontals or verticals I can figure out sumifs or sumproducts but I can't seem to get it to work.
For example, in the picture, if it was working correctly, the answer would be 45.
Please help. Thank you.
 

Attachments

  • excel help image.JPG
    excel help image.JPG
    66 KB · Views: 10
  • excelhelp picture.JPG
    excelhelp picture.JPG
    76.7 KB · Views: 9

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try:

Dante Amor
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3123456789101112123456789101112
4ACTACTACTACTACTACTACTACTACTACTACTACTFCSTFCSTFCSTFCSTFCSTFCSTFCSTFCSTFCSTFCSTFCSTFCST
5ORANGESCAT1123456789101112111111111111
6APPLESCAT1123456789101112111111111111
7GRAPESCAT1123456789101112111111111111
8PINEAPPLESCAT1123456789101112111111111111
9ORANGESCAT2123456789101112111111111111
10APPLESCAT2123456789101112111111111111
11GRAPESCAT2123456789101112111111111111
12PINEAPPLESCAT2123456789101112111111111111
13
21
22ORANGESCAT1ACT945
Hoja1
Cell Formulas
RangeFormula
E22E22=SUMPRODUCT(($A$5:$A$12=A22)*($B$5:$B$12=B22)*($C$4:$Z$4=C22)*($C$3:$Z$3<=D22)*($C$5:$Z$12))


You must put the operator <= in the formula and not in the cell.

🤗
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top