sumif dynamic criteria range

[Luigi802]

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!

 

[Eric W]

You seem to be confusing the sheet name with the table name. However, look at the following:

	C	D	E	F	G	H	I	J	K	L
1			Column1	Column2	Column3		Column1	Column2	Column3
2			1	10	a		4	40	b
3	Table2		2	20	b		5	50	a
4	Column3		3	30	c		6	60	c
5	a
6
7	50

<tbody>
</tbody>

Sheet17​

Worksheet Formulas C7 =SUMIF(INDIRECT(C3&"["&C4&"]"),C5,INDIRECT(C3&"[Column2]")) <tbody> </tbody>

<tbody>
</tbody>

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.

 

[Luigi802]

You seem to be confusing the sheet name with the table name. However, look at the following:

	C	D	E	F	G	H	I	J	K	L
1			Column1	Column2	Column3		Column1	Column2	Column3
2			1	10	a		4	40	b
3	Table2		2	20	b		5	50	a
4	Column3		3	30	c		6	60	c
5	a
6
7	50

<tbody>
</tbody>

Sheet17​

Worksheet Formulas C7 =SUMIF(INDIRECT(C3&"["&C4&"]"),C5,INDIRECT(C3&"[Column2]")) <tbody> </tbody>

<tbody>
</tbody>

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.

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

Day	Date	Amount	Description	Category
Sunday	18-09-18	563	A	1c
Tuesday	20-09-18	567	A	3d
Wednesday	15-08-18	23	B	5t
Friday	03-01-18	7	A	6k
Saturday	11-10-18	12	C	9f

<tbody>
</tbody>

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

 

[Eric W]

This works for me:

	A	B	C	D	E	F	G	H	I	J
1					Day	Date	Amount	Description	Category
2					Sunday	9/18/2018	563	A	1c
3					Tuesday	9/20/2018	567	A	3d
4		Search column	Category		Wednesday	8/15/2018	23	B	5t
5		Search value	5t		Friday	1/3/2018	7	A	6k
6		Sum	23		Saturday	10/11/2018	12	C	9f

<tbody>
</tbody>

Sheet2

Worksheet Formulas Cell Formula C6 =SUMIF(INDIRECT("Table1["&C4&"]"),C5,Table1[Amount]) <tbody> </tbody>

<tbody>
</tbody>

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.

 

[Luigi802]

This works for me:

	A	B	C	D	E	F	G	H	I	J
1					Day	Date	Amount	Description	Category
2					Sunday	9/18/2018	563	A	1c
3					Tuesday	9/20/2018	567	A	3d
4		Search column	Category		Wednesday	8/15/2018	23	B	5t
5		Search value	5t		Friday	1/3/2018	7	A	6k
6		Sum	23		Saturday	10/11/2018	12	C	9f

<tbody>
</tbody>

Sheet2

Worksheet Formulas Cell Formula C6 =SUMIF(INDIRECT("Table1["&C4&"]"),C5,Table1[Amount]) <tbody> </tbody>

<tbody>
</tbody>

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.

That simple huh? Well thank you very much that worked perfectly!

 

[Eric W]

Happy to help!