Reconciling Christmas Spending with SUMIFS, IF and Good Spreadsheet Layout

/in /by

This video revisits a challenge from a few years ago. 3 siblings needed a simple way to reconcile who owed what to whom after all the Christmas gits had been purchased. For example:

If Bo agreed to chip in $200 on gifts that Jay bought, and Jay agreed to chip in $90 on gifts purchased by Bo, what’s an easy way to see that Bo owes Jay $110? But there’s a 3rd sibling, Tanya who needs to be considered.

I solved this a few years ago when Power Query wasn’t available. I decided to revisit this and us Power Query but it proved to complicated. I had multiple queries, joins, custom and conditional columns … the Power Query solution was overkill.

This showed that it’s important to look at what’s at stake and use what’s appropriate. For this challenge, I did use Power Query’s unpivot, but the real work was done by SUMIFS, IF, and good layout.

Here is the 26 Best Microsoft Excel Tips of 2019 podcast
https://www.myexcelonline.com/podcast/026-the-best-microsoft-excel-tips-tricks-in-2019/
#SUMIFS
#UNPIVOT
#PowerQuery

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2