SUMIFS with INDEX MATCH and greater than?

B

BroadSpectrum

New Member
Joined
Oct 18, 2019
Messages
5
Hello, I hope I can explain this properly as english is not my native tongue.

the first table takes its information from the bottom table that is usually in another worksheet by using Index Match

I've been trying to make my 3rd column (2+) add the amount from the Indexed Matched row if the age in days (worksheet 2) is greater than 1 day.


I realize my english may not explain what i'm really achieve but i'm willing to answer questions if you have any



ABCDEFGHI
1Age in Days (worksheet 1)12+
2Total from another thing
3Total11#VALUE!
4Queue 10#VALUE!
5Queue 21
6Queue 30
7Queue 44
8Queue 56
9
10Age in days (worksheet 2)125915172325
11Total from another thing3212211343453121
12Total11710125161015
13Queue 212531634
14Queue 442341525
15Queue 563253556

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B3=SUM(B4:B8)
C3=SUM(C4:C7)
B4=IFNA(INDEX($B$13:$E$16,MATCH($A4,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
C4=SUMIFS(B13:C14,A13:A14,A4,B10:C10,">"&B1)
B5=IFNA(INDEX($B$13:$E$16,MATCH($A5,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B6=IFNA(INDEX($B$13:$E$16,MATCH($A6,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B7=IFNA(INDEX($B$13:$E$16,MATCH($A7,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B8=IFNA(INDEX($B$13:$E$16,MATCH($A8,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B12=SUM(B13:B15)
C12=SUM(C13:C15)
D12=SUM(D13:D15)
E12=SUM(E13:E15)
F12=SUM(F13:F15)
G12=SUM(G13:G15)
H12=SUM(H13:H15)
I12=SUM(I13:I15)

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Perhaps in C2 and fill down.

=IFERROR(SUM(INDEX($C$11:$I$15,MATCH($A2,$A$11:$A$15,0),0)),0)
 
Upvote 0
Super sorry, it seems like i have messed up the question.

I would like it to be the cell C3 and down to be where it it would add the corresponding rows! the 2nd rows from each table is actually a total for another file!
Currently, i have the formula =SUMIFS(B13:C14,A13:A14,A4,B10:C10,">"&B1) in C3 but that obviously didnt do the trick :|
 
Upvote 0
would anyone happen to know how i could plug in SUMIF/S into =IFNA(INDEX($B$13:$E$16,MATCH($A5,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)

i suppose essentially what i'm thinking of is that as long as the number in $B$10:$E$10 is > B$1 then it is to be summed.

In any case, thanks jason for trying! i appreciate it
 
Upvote 0
I think I get the idea of what you're asking but just to be sure, could you post your example again, but this time type in the answers that you want in C2:C8 (if you want additional results in the other columns then doing D2:D8 as well would be great).
 
Upvote 0

Book1
ABCDEFGHI
1Age in Days (worksheet 1)12+
2row for another thing----
3Total1146
4Queue 100
5Queue 2124
6Queue 30
7Queue 4422
8Queue 5629
9
10Age in days (worksheet 2)125915172325
11row for another thing----------------
12Total11710125161015
13Queue 212531634
14Queue 442341525
15Queue 563253556
Sheet1
Cell Formulas
RangeFormula
B3=SUM(B4:B8)
B4=IFNA(INDEX($B$13:$E$16,MATCH($A4,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B5=IFNA(INDEX($B$13:$E$16,MATCH($A5,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B6=IFNA(INDEX($B$13:$E$16,MATCH($A6,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B7=IFNA(INDEX($B$13:$E$16,MATCH($A7,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B8=IFNA(INDEX($B$13:$E$16,MATCH($A8,$A$13:$A$16,0),MATCH(B$1,$B$10:$E$10,0)),0)
B12=SUM(B13:B15)
C3=SUM(C4:C7)
C4=IFERROR(SUM(INDEX($C$13:$I$15,MATCH($A4,$A$13:$A$15,0),0)),0)
C5=SUM(C13:I13)
C7=SUM(C14:I14)
C8=SUM(C15:I15)
C12=SUM(C13:C15)
D12=SUM(D13:D15)
E12=SUM(E13:E15)
F12=SUM(F13:F15)
G12=SUM(G13:G15)
H12=SUM(H13:H15)
I12=SUM(I13:I15)


Thanks again Jasonb75 for your time! So, ultimately, since Queue 2, queue 4 and queue 5 exists in worksheet 2, it would pull their row data and anything that is over 1 day will be summed up in their respective rows
 
Upvote 0
I think that I have it right this time, Sumproduct will be easier to work with than sumifs but it is less efficient, if this is too slow in your real file then we can look at alternatives to improve performance.

Try this one in B3, then fill down and right as needed. Note that C1 needs to be 2 not 2+ If you want the + symbol at the end, try a custom number format of #\+

=SUMPRODUCT(($A$11:$A$15=$A3)*($B$10:$I$10>=B$1)*($B$10:$I$10<IF(C$1="",1E+100,C$1)),$B$11:$I$15)

I've added a bit of extra flexibility to the formula in case it is needed, try changing C1 to 5 and entering 15 in D1 to see the difference.
 
Upvote 0
Hello thanks again! Hey, listen, i'm starting to feel guilty about all of this and i don't really know how any of this works as i just recently got into Excel but would it at all be out of line to offer you some money to buy a beer with (this is not all tied into solving my question. just the fact that you've taken the time to help)?

While your formula actually worked great on a fixed range, i was hoping for one that i could plug into a whole worksheet that has different, in this case, fruits.

The 2nd worksheet changes everyday. For this, Bananas 1, 3 and 6 came in but tomorrow it could be Bananas 2,4 and 6 or perhaps all 6 queues(there could actually be up to 15 banana queues). After the Bananas table another table for another product (this report comes in like that externally and dont really have control over it) comes in and then another one after that. So i was more so looking for something that could be extended towards a dynamic range that could just be stretched even into blank cells or cells containing texts.

ABCDEFGHIJKL
1Age in Days (worksheet 1)12/+
2row for another thing----
3Total546
4Bananas 1124
5Bananas 200
6Bananas 3422
7Bananas 400
8Bananas 500
9Bananas 63229
10
11FROM ANOTHER WORKSHEET
12Age in days (worksheet 2)125915172325
13Total37710125161015
14Bananas 112531634
15Bananas 342341525
16Bananas 6323253556
17
18Age in days (worksheet 21235917426488111135
19Total323365385911143743285828361090
20Strawberries 30432132645651363113163213
21Strawberries 72121212134653734312825
22Strawberries 817322322321324231438645852

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B3=SUM(B4:B8)
C3=SUM(C4:C7)
B4=IFNA(INDEX($B$14:$E$17,MATCH($A4,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C4=SUMPRODUCT(($A$14:$A$16=$A4)*($B$12:$J$12>B$1)*($B$14:$J$16))
B5=IFNA(INDEX($B$14:$E$17,MATCH($A5,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C5=SUMPRODUCT(($A$14:$A$16=$A5)*($B$12:$J$12>B$1)*($B$14:$J$16))
B6=IFNA(INDEX($B$14:$E$17,MATCH($A6,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C6=SUMPRODUCT(($A$14:$A$16=$A6)*($B$12:$J$12>B$1)*($B$14:$J$16))
B7=IFNA(INDEX($B$14:$E$17,MATCH($A7,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C7=SUMPRODUCT(($A$14:$A$16=$A7)*($B$12:$J$12>B$1)*($B$14:$J$16))
B8=IFNA(INDEX($B$14:$E$17,MATCH($A8,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C8=SUMPRODUCT(($A$14:$A$16=$A8)*($B$12:$J$12>B$1)*($B$14:$J$16))
B9=IFNA(INDEX($B$14:$E$17,MATCH($A9,$A$14:$A$17,0),MATCH(B$1,$B$12:$E$12,0)),0)
C9=SUMPRODUCT(($A$14:$A$16=$A9)*($B$12:$J$12>B$1)*($B$14:$J$16))
B13=SUM(B14:B16)
C13=SUM(C14:C16)
D13=SUM(D14:D16)
E13=SUM(E14:E16)
F13=SUM(F14:F16)
G13=SUM(G14:G16)
H13=SUM(H14:H16)
I13=SUM(I14:I16)
B19=SUM(B20:B22)
C19=SUM(C20:C22)
D19=SUM(D20:D22)
E19=SUM(E20:E22)
F19=SUM(F20:F22)
G19=SUM(G20:G22)
H19=SUM(H20:H22)
I19=SUM(I20:I22)
J19=SUM(J20:J22)
K19=SUM(K20:K22)
L19=SUM(L20:L22)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Any help that we offer is completely free, see number 5 here https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

From your example, I think that your source data is not formatted in a way that you could make the formulas dynamic, possibly reports could be generated wth vba (a macro) but that is not something that I will be able to help you with.

Hopefully other members will look at your thread and have some ideas that I'm not thinking of.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
125
Peter_SSs
Peter_SSs
A
  • Solved
Index/Match formula or other method?
Replies
3
Views
175
Peter_SSs
Peter_SSs
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
313
KRice
K
W
  • Question
IF Logic HELP
Replies
7
Views
151
SanjayGMusafir
SanjayGMusafir
W
  • Solved
Index/Match Cannot The Find Column
2
Replies
11
Views
749
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,376
Messages
6,119,172
Members
448,870
Latest member
max_pedreira

Share this page

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