SUMIFS using multiple criteria from same table column

mattjpep

New Member
Joined
Mar 8, 2016
Messages
31
I have the following formula:

SUM(SUMIFS(SD[Gross Amt],SD[FY],[@FY],SD[Period],AY$1,SD[Employee ID],[@[Emp ID]],SD[Title Code],{[@[Ttl Chg]],[@[Title Code]]}))

The part in bold is what I need help with. Typically, you would use the braces for multiple criteria in the same column, however I'm not sure how to adapt this for criteria from a table. Is this even possible? I appreciate any help. Thanks.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Anand Sharma

Board Regular
Joined
Nov 22, 2016
Messages
63
Hi

Below is structure of Sumifs

SUMIFS(Range you want to sum, range having criteria , your criteria, criteriaRange2, criteria2, .....continue)
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
The problem with structured tables (for me, at least) is how formulas get structured - sometimes to the point of making reading them really hard. What exactly is that formula doing?
 

mattjpep

New Member
Joined
Mar 8, 2016
Messages
31
This is pulling data from a table:

=SUM(SUMIFS(SD[Gross Amt],SD[FY],[@FY],SD[Period],AY$1,SD[Employee ID],[@[Emp ID]],SD[Title Code],{[@[Ttl Chg]],[@[Title Code]]}))

The red is the data I'm totaling
The blue is looking for the year in the table and referencing the year on my other tab for a match.
The orange is looking for the period and referencing the period on my other tab for a match.
The pink is looking for the employee ID and then referencing the Emp ID column in my table for a match.
The green is looking for the title code and then referencing the title code is two different columns for a match.

This is a formula I use all the time, with one exception. I usually have specific data (i.e. {"Title1","Title2"}) where the bold currently is. In this formula, I'm using table column names and it's giving me an error. Is there another way to use the table column names?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,812
Messages
5,638,489
Members
417,028
Latest member
JFCLUK

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
Top