SUMIFS Spill Error

M

mprocacc

New Member
Joined
Jun 29, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
TIA!

I have a very simple goal that I cannot figure out! I want to use a spill formula to create a two dimensional result that summarizes dollars by serial number across months. I added helper Column1 in desperation - it calculates the MONTH of Date Posted. With the helper column, =SUMIFS(Table1[Amount],Table1[Column1],N12#,Table1[Serial Account No],M13#) works like a charm!

I first created the months across in N12 by: =TRANSPOSE(UNIQUE(MONTH(Table1[Date Posted]))) and then
I created the Serial Numbers down in M13 by: =SORT(UNIQUE(Table1[Serial Account No]))

But I HATE helper columns! Is there a way to generate a SPILL result (see image at bottom) without a helper column?? Here's some of my data.

Date PostedSerial Account NoCheck NoAmountColumn1
01/02/241157033101
01/02/241157133101
01/02/241158133101
01/02/241160012573101
01/02/241162533101
01/02/241164032901
01/02/241164363383101
01/02/241164833101
01/02/241164933101
01/02/24116592093101
01/02/241167133101
01/02/241172432901
01/02/241172732901
01/03/241156913101
01/03/241157213101
01/03/241157313101
01/03/241157613101
01/03/241157713101
01/03/241158213101
01/03/241158313101
01/03/241158513101
01/03/241158813101
01/03/241158913101
01/03/241159113101
01/03/241159213101
01/03/241159313101
01/03/241159413101
01/03/241159513101
01/03/241159613101
01/03/241159713101
01/03/241160213101

And here's what I want to achieve:

12345678910
11569310310310310310310310310310310
11570310310310310310310310000
115713106203103100620310310310310
11572310310310310310310310310310310
11573310310310310310310310310310310
1157431031031031093006203103100
11575310310310310310310310310310310
11576310310310310310310310310310310
11577310310310310310310310310310310
11578310310310310310310310310310310
11579310310310310310310310310310310
11580310310310310310310310310310310
115816200310620310062006200
11582310310310310310310310310310310
11583310310310310310310310310310310
11584310310310310000000
11585310310310310310310310310310310
1158631006203103103103103100310
115873103103103103103100000
11588310310310310310310310310310310
11589310310310310310310310310310310
115903106203103103103103103103100
11591310310310310310310310000
11592310310310310310310310310310310
11593310310310310310310310310310310
11594310310310310310310310310310310
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try either PIVOTBY:
Excel Formula: 
=PIVOTBY(Table1[Serial Account No],MONTH(Table1[Date Posted]),Table1[Amount],SUM,0,0,,0)
...or MMULT:
Excel Formula: 
=LET(
    mm, MONTH(Table1[Date Posted]),
    um, TOROW(UNIQUE(SORT(mm))),
    sn, Table1[Serial Account No],
    un, UNIQUE(SORT(sn)),
    rs, MMULT(--(un=TOROW(sn)),(um=mm)*Table1[Amount]),
    HSTACK(VSTACK("",un),VSTACK(um,rs))
)
 
Upvote 0
Whenever I think I'm pretty good with excel...somebody like djclements makes it clear!

1736523942579.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
137
Chris258
C
C
  • Question Question
SUMIFS if a date is within the range
Replies
1
Views
734
Fluff
Fluff
H
  • Question Question
Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually
Replies
2
Views
810
felixstraube
F
P
  • Question Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
450
POLACOLOCO
P
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
1K
Insurance Dave
I

Forum statistics

Threads
1,225,726
Messages
6,186,674
Members
453,368
Latest member
xxtanka

Share this page

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
Back
Top