If this (Formula) or If that (formula)

I need a column to have more than one formula. For example. I have employees that work in different cities/counties. Based on where they work, there may be a City and County Withholding tax or just A City Withholding tax or just a county withholding tax.
A) I have an employee working in Madisonville - there is a city of Madisonville tax (2.5%) and a Hopkins County withholding tax (0.5%)
B) An Employee working in Hanson only has to pay the Hopkins Co Withholding tax (0.5%) So my Hopkins Co Column needs multiple "If" formulas.
For A) I have Location column that would say Madisonville and a formula =SUMIF(Location@row, "City Tax:Madisonville", [Total Pay]@row) * 0.025 for the Madisonville Column and =SUMIF(Lcoation@row, "City Tax:Madisonville", [Total Pay]@row) * 0.005 for the Hopkins Co Column.
But for Employee B I would need =sumif(Location@row,"City Tax:Hanson",[Total Pay]@row)*.005
How can I have both of these formulas in the same Hopkins Co Tax Column?
Answers
-
-
-
I would suggest setting up a table that looks similar to this except it only has each location listed out once. Then across the columns you would enter the percentages. From there you can use an INDEX/MATCH to pull in the appropriate percentage because right now it looks like you may be getting into a pretty long and unwieldy nested IF otherwise.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!