How do I create a formula to get the total $ in the Utilities Category (K6 - Orange Cell)?
I need to use the first table to get ALL of the Account Numbers associated with Utilities and then add the $ associated with those account numbers in the second table.
Is there a formula for that?
Please try at K5
=SUM(SUMIFS($H$4:$H$11,$F$4:$F$11,IF($B$4:$B$11=J5,$C$4:$C$11)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
v A B C D E 1 Category Total 2 Rent 1200 Category Sum of Total 3 Utilities 120 Entertainment 159 4 Entertainment 140 Food 209 5 Utilities 25 Rent 1200 6 Utilities 67 Utilities 212 7 Food 150 8 Entertainment 19 9 Food 59
This was accomplished using power Query. Full join of the two tables in PQ and then creating a pivot table. See attached file for analysis
Last edited by alansidman; 04-01-2020 at 09:00 AM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi, there
Use defined names "CAT" and "ACC" for the datasets respectively and the formulae will do the trick.
Formula:
Please Login or Register to view this content.
Pls refer to the attched file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks