sumif dynamic criteria range

L

Luigi802

Board Regular
Joined
Oct 16, 2014
Messages
80
So I'm trying to create a sumif formula that looks something like this =sumif(Sheet1[C4],C5,Sheet1[Column2])
So the "C4" in "Sheet1[C4]" is a dynamic value that corresponds to a column header name in a table, "C5" is obviously the criteria, and again obviously the "Sheet1[Column2]" is the column in which I want to sum. I've scoured the internet and just can't find what I'm looking for!:confused:
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
You seem to be confusing the sheet name with the table name. However, look at the following:

CDEFGHIJKL
Column1Column2Column3Column1Column2Column3
1040
Table2ba
Column3c60c
a
50

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]b[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas [TABLE="width: 100%"]
<tbody>[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=SUMIF(INDIRECT(C3&"["&C4&"]"),C5,INDIRECT(C3&"[Column2]"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



E1:G4 is defined as Table1, and I1:K4 is Table2. Then you can use the formula in C7 to get a total based on the values in C3:C5. If this isn't exactly what you're looking for, you should be able to get the idea. The INDIRECT function is the key. Hope this helps.
 
Upvote 0
Eric W said:
You seem to be confusing the sheet name with the table name. However, look at the following:

CDEFGHIJKL
Column1Column2Column3Column1Column2Column3
1040
Table2ba
Column3c60c
a
50

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]b[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas [TABLE="width: 100%"]
<tbody>[TR]
[TH]C7[/TH]
[TD="align: left"]=SUMIF(INDIRECT(C3&"["&C4&"]"),C5,INDIRECT(C3&"[Column2]"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



E1:G4 is defined as Table1, and I1:K4 is Table2. Then you can use the formula in C7 to get a total based on the values in C3:C5. If this isn't exactly what you're looking for, you should be able to get the idea. The INDIRECT function is the key. Hope this helps.
Click to expand...

Yeah sorry, I accidentally put the sheet name cause it's the same as my table name. Here's an example of what I'm trying to do

[TABLE="width: 384"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD="align: right"]18-09-18[/TD]
[TD="align: right"]563[/TD]
[TD] A[/TD]
[TD]1c[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]20-09-18[/TD]
[TD="align: right"]567[/TD]
[TD] A[/TD]
[TD]3d[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"]15-08-18[/TD]
[TD="align: right"]23[/TD]
[TD] B[/TD]
[TD]5t[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]03-01-18[/TD]
[TD="align: right"]7[/TD]
[TD] A[/TD]
[TD]6k[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD="align: right"]11-10-18[/TD]
[TD="align: right"]12[/TD]
[TD] C[/TD]
[TD]9f[/TD]
[/TR]
</tbody>[/TABLE]

So let's say this is my table (table1) so my formula would be something kinda like before =sumif(table1[C4],C5,table1[amount]) and C4 would be and 1 of the following table headers; Day, Date, Amount, Description, Category
So if C4 was "Category" and C5 was "5t" the result would be 23
I tried the formula you posted but if gave me a #REF! error
 
Last edited:
Upvote 0
This works for me:

ABCDEFGHIJ
DayDateAmountDescriptionCategory
SundayA1c
TuesdayA3d
Search columnCategoryWednesdayB5t
Search value5tFridayA6k
SumSaturdayC9f

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9/18/2018[/TD]
[TD="align: right"]563[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9/20/2018[/TD]
[TD="align: right"]567[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/11/2018[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=SUMIF(INDIRECT("Table1["&C4&"]"),C5,Table1[Amount])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Change the values in C4 and C5, and the formula will show different results.

If you still have problems, what version of Excel do you have? Also, based on your date formats, you may have a version of Excel with different defaults. You might need to change all the commas in the formula to semicolons.
 
Upvote 0
Eric W said:
This works for me:

ABCDEFGHIJ
DayDateAmountDescriptionCategory
SundayA1c
TuesdayA3d
Search columnCategoryWednesdayB5t
Search value5tFridayA6k
SumSaturdayC9f

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9/18/2018[/TD]
[TD="align: right"]563[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9/20/2018[/TD]
[TD="align: right"]567[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10/11/2018[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=SUMIF(INDIRECT("Table1["&C4&"]"),C5,Table1[Amount])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Change the values in C4 and C5, and the formula will show different results.

If you still have problems, what version of Excel do you have? Also, based on your date formats, you may have a version of Excel with different defaults. You might need to change all the commas in the formula to semicolons.
Click to expand...


That simple huh? Well thank you very much that worked perfectly!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dynamic month selection to SUMIF (Tried XLOOKUP)
Replies
2
Views
100
djclements
djclements
Richard1982
  • Solved
=SUMIF() question : can the criteria be a range of things?
Replies
9
Views
694
arthurbr
arthurbr
S
  • Question Question
SUMIF and Search
Replies
2
Views
407
Super Mario
S
N
  • Solved
Multiple Sumif across columns
Replies
4
Views
748
Fluff
Fluff
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
206
Ramses505
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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