We help IT Professionals succeed at work.

Don't want to write a bunch of sumif statements

sullisnyc44
sullisnyc44 asked
on
I have a matric table that lists all the states with tax conditions.

for example
             Condition A          Condition B          Condition C
NY                 T                         E                              T
TX                  E                          E                              T

I have a another spreadsheet with all those conditions as headers and line items with various states.

If the item for Condition A is in TX then the item is taxable, if not it is exempt then it's 0.

The issue is that I don't want to write 9 combined if statesments (based on the headers). How can I create a more efficient formula?

My column titles are called Headings and all of my data is called alldata on the matrix spreadsheet.
Comment
Watch Question

BRONZE EXPERT
Most Valuable Expert 2013

Commented:
I'm finding it hard to visualise - can you post a sample sheet with expected results?

regards, barry
SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

as a starting point, see attached. The table you described above is used as a lookup source for an Index/Match. The formula can be wrapped into an IF statement to return 0 if the result is "e".

=INDEX($A$1:$D$3,MATCH(K2,$A$1:$A$3,0),MATCH(J2,$A$1:$D$1,0))

If that is not going in the right direction, please post a file with your data structure.

cheers, teylyn
Book3.xlsx

Author

Commented:
Thanks so much.

That's exactly right.

=IF(INDEX(AllData,MATCH($J2,States,0),MATCH(N$1,Headings,0))="T",N2,0)

What I wanted to avoid was writing nine of these and/or adding more columns.

Is there a way to loop through the headings of a given range and perform the formula above?

Do I need a macro?

SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Again, please post a sample file with your data structure and range names in place, because from the formula above it's not possible to see that.

What range does "States" refer to? Or "Headings"? How exactly is the range name defined? Details matter.

What cells do you want to "loop" through? What is the expected result? If you can supply that in an Excel file, we know what to aim for. That will be much better than descriptions of a file we can't see.

cheers, teylyn

Author

Commented:
sorry. just took me a while to clean up the sheets.

I want to find the total taxable amount per row.

so I reference the 'matrix' spreadsheet to find out if it's taxable in a particular state.

I added a 'taxable' column for each header which will determine whether it's taxable in the state and if it is bring forward that total.

then sum all those taxable columns

I just thought there is probably a better, more efficient, more concise way of doing things.
TaxExample.xlsx
SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Not quite sure that it's possible to sum all these lookups into one formula. If it can be done, barry will be able to do it.

To keep the workbook nice and clean, I'd put all the columns for Taxable next to each other, followed by a new column that creates a sum across all taxable columns, then hide the taxable columns. This will give you the same effect than doing it in one cell, and it's probably even faster with helper columns than with a big all-in-one array formula.

See attached. Columns Y to AI are hidden. AJ shows the sum of taxable amounts

cheers, teylyn


TaxExample.xlsx
BRONZE EXPERT
Most Valuable Expert 2013
Commented:
In your Matrix you have two column Ks and an "L" so I deleted the last two and deleted your additional columns.

You can then use a SUMIF formula like this in Z2 copied down

=IF(J2="","",SUMIF(INDEX(Matrix!B$4:L$30,MATCH(J2,Matrix!A$4:A$30,0),0),"T",N2:X2))

That will sum every column where the relevant row from the Matrix sheet is "T"

The total are a little different - probably because of the columns I deleted...but Z3 is 17 because it sums A, B, C and K (those columns with T for WA).

see attached

regards, barry
27426654.xlsx
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
Note: this assumes that the columns Header A to Header K will be in the same order in each sheet as per the example - is that the case?

barry
SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
nice!

Author

Commented:
yes they are in the same order.

that's awesome.

so if I use my named ranges the formula would read:

=IF(J2="","",SUMIF(INDEX(AllData,MATCH(J2,States,0),0),"T",N2:X2))

I will give it a try and let you know! thanks!

Author

Commented:
hmmm the formula in the spreadsheet definitely works but gives mr circular reference errors if I try to use a named range.

why is that?

ps- noticed that I had limit my AllData to column B

pps - this is awesome. I thank you
SILVER EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
What is the formula that defines AllData?
Where did you put the formula barry posted?
BRONZE EXPERT
Most Valuable Expert 2013

Commented:
I had the same problem with circular references when I tested. That's because you have extra columns in AllData as I stated previously. Because there were 13 columns in Alldata rather than the 11 in N2:X2 then the sum range actually gets implicitly changed to be 13 columns also, i.e. N2:Z2, so if you put the formula in Y2 or Z2 you get a circular reference.

You need to make sure the number of columns match. In Alldata you had 2 Header Ks and also a Header L whereas N2:X2 is just A to K

regards, barry

Explore More ContentExplore courses, solutions, and other research materials related to this topic.