Trying to nest SUMPRODUCT

Trying to sum payment data from a separate workbook, based upon values in the current workbook. I need to query Column O against a Named Range (DO) on the other book, but only where values in Column O aren't "See Detail" or "NA".

I want to do something like:
=SUMPRODUCT('Payment Data.xlsx'!DO=SUMPRODUCT(O9:O12<>"See Detail",O9:O12<>"NA"),'Payment Data.xlsx'!Amount)

How can I get the second SUMPRODUCT to return an array of values? I also just tried straight matches with:
=SUMPRODUCT('Payment Data.xlsx'!DO=O9:O12,'Payment Data.xlsx'!Amount)

I thought it might have to do with the mix of text and numbers in O9:O12, so I limited it to just the numerical values in O11:O12, and I still can't get the sum of Amount that I want.

Any thoughts or insight would be greatly appreciated!

So, I can't see exactly what you're looking at, but SUMPRODUCT doesn't return arrays. Why don't you just do one SUMPRODUCT and use parentheses to group as needed?

I don't know what the DO range looks like, but this would this work?

Formula:

Please Login or Register  to view this content.

I'm assuming the DO range is the same dimensions as O9:O12.

Still returns #N/A.

DO range is much larger. Single column, but over 15,000 rows.

That's the issue then. SUMPRODUCT expects ranges of the same dimensions. What exactly are you trying to accomplish?

I've got payment data I need to sum. That's the named range Amount in the previously posted formula. I need to sum those areas where the ID number in named range DO matches the values in Column O of the current spreadsheet.

This is horribly easy with a single ID, ie, Sumproduct(DO=O11, Amount)

The issue here is that I have groups of values, and I'd like to collectively sum multiple IDs.

Once this is sorted I'll add a Job Number check to it (also a defined range on the Payment sheet, column Q on the current sheet).

Current worksheet arranged like:

See Detail	See Detail	(Roll Up Sumproduct formula)
NA	Job Number One	(no payment info because the ID is NA)
14014555	Job Number 2	(this sumproduct works)
14012563	Job Number 3	(this sumproduct works)

The bolded area is where I need the help. It's easy enough to just sum(T9:T12), *but* the formulas in 11 and 12 won't be protected as they can be manually adjusted. Accordingly, I need to make sure that 11 and 12 (since 10 is blank or 0) match the total of the payments off the payment sheet.

Hence why it made sense to me to try and do something like (Sumproduct(DO=(Sumproduct((O9:O12<>"See Detail)*(O9:O12<>"NA")),Amount)

Can't upload from work, and it won't import from Google Drive. Can I drop a link here to the file on my Drive account? And yes, it's all for Excel. Drive is just the only place I can easily put the file right now.

I understand the hesitation on file sharing sites. Hence I asked. Here's my underlying payment date (made in a super simple and user-friendly format):

DO	Job Number	Amount
14014555	H117GG10	75000
14012563	H1211L30	5000
14012563	H1211L30	5000
14012563	H1211L30	5000
14014288	H1177SLZ	905
14014286	H1177SLZ	3250
14014289	H1177SLZ	2845
14014288	H1177SLZ	700
14014286	H1177SLZ	6310
14014289	H1177SLZ	3240
14014288	H1177SLZ	835
14014289	H1177SLZ	2375
14014286	H1177SLZ	4495
14014289	H1177SLZ	3375
14014286	H1177SLZ	5055
14014288	H1177SLZ	680
14014289	H1177SLZ	2450
14014288	H1177SLZ	810
14014286	H1177SLZ	4085
14014288	H1177SLZ	875
14014289	H1177SLZ	2735
14014286	H1177SLZ	5110
14014288	H1177SLZ	405
14014286	H1177SLZ	5220
14014288	H1177SLZ	490
14014286	H1177SLZ	4215
14014289	H1177SLZ	2740
14014289	H1177SLZ	3165
14014288	H1177SLZ	1225
14014286	H1177SLZ	4895

And my roll up:

Ref Doc ID	Job Number	Invoices Paid Thru 5-31 (Balance to YTD payment spreadsheet)
See Detail	See Detail	0
NA	H1211800	0
14014555	H117GG10	75000
14012563	H1211L30	15000
See Detail	See Detail
14014286	H1177SLZ	42635
14014289	H1177SLZ	22925
14014288	H1177SLZ	6925

On this table, numerical values in what would be Column C are the result of =SUMPRODUCT(DO=A5,JobNumber=B5,Amount)

What I would LOVE is for C3 here to be able to SUMPRODUCT A5 and A6, with B5 and B6.

Same thing with C8. While B9-11 all has the same job number, I only want charges to that job number where the ID matches A9:A11.

I do hope that this makes a bit more sense.

I can download, just not upload.

And that's exactly what I've got right now. (Sidenote: sumif vs sumproduct, discuss)

Ultimately, those formulas in I go in H. So let's pretend you did that.

Those formulas won't be protected, so that hardcoded changes can be made. But what I'd love to do (and the purpose of this thread) is have, for example, H7 in your book do something like: Grab the 3 ID numbers in F, add up all the payments for the corresponding job codes in G, and compare that to the sum of H8:H10.

Same thing for H2, though it would ignore F3 as it's NA.

I've seen and done some really fun things in Excel. I can't wrap my head around this one though.

Short story is there isn't much logic to it that would sense. Longer answer is that this is financial tracking for contract expenditures. We might have what we call a Master Agreement, which will group individual Doc Refs (one per Provider). A Doc Ref may have a single job number, or up to ten job numbers. There's absolutely no logic in the job numbers that I can figure out. It's a horrible system we're forced to use, and a format and layout that we can't deviate from. So far I've made a lot of things work really well, even if I have to get a little convoluted.

In all, I've got 7 workbooks like this that will tie back to the single Payments workbook, and each of those 6 workbooks has ~140 rows of data.

If need be, I can combine it all and create a helper spreadsheet in the Payments book. Like most of you fine folks, I hate helper columns.

I just can't see any other way right now.

OK, now I have 0 in all I cells, and J2 and J7 show the total sum of the entirety of C.

Where'd I go wrong?

That's what I get for not pasting I all the way down and skipping that blank row, eh? I opened that file up and suddenly the light went on. Sly little genius, you are.

I think I can use the heck out of a helper column like that. I really like that creative solution there. Now if I can just get myself to the point where I can see those solutions on my own...

Still tinkering with the original idea. This little gem works: =SUMPRODUCT((--(A2:A31=F8))+(--(A2:A31=F9))+(--(A2:A31=F10)),C2:C31)

Now if I could just set that dynamically. But I'm not sure I can without going through VBA.

Good call, Tony. If you caught this before the edit, I am eating crow as we speak. If you didn't, there was no edit.

Tony, you freaking nailed it. Absolutely perfect solution that works with a dynamic range (in lieu of F8:F10), and SUMIFS so I can use the job code as a criteria as well.

Very well done!