Trying to sum payment data based on two criteria: A doc ID and a Job Number. Trouble is I where I want the formula is essentially a "roll up" row for individual data sets, meaning I can have multiple Doc IDs and Job Numbers. Sample Data:

[TABLE="class: grid, width: 260"]

[tr]

[td]

DO

[/td]

[td]

Job Number

[/td]

[td]

Amount

[/td]

[/tr]

[tr]

[td]

14014555

[/td]

[td]

H117GG10

[/td]

[td]

75000

[/td]

[/tr]

[tr]

[td]

14012563

[/td]

[td]

H1211L30

[/td]

[td]

5000

[/td]

[/tr]

[tr]

[td]

14012563

[/td]

[td]

H1211L30

[/td]

[td]

5000

[/td]

[/tr]

[tr]

[td]

14012563

[/td]

[td]

H1211L30

[/td]

[td]

5000

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

905

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

3250

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

2845

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

700

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

6310

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

3240

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

835

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

2375

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

4495

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

3375

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

5055

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

680

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

2450

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

810

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

4085

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

875

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

2735

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

5110

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

405

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

5220

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

490

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

4215

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

2740

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

3165

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

1225

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

4895

[/td]

[/tr]

[/TABLE]

Roll Up Examples:

[TABLE="class: grid, width: 453"]

[tr]

[td]

Ref Doc ID

[/td]

[td]

Job Number

[/td]

[td]

Invoices Paid

[/td]

[/tr]

[tr]

[td]

See Detail

[/td]

[td]

See Detail

[/td]

[td]

**1** (formula to go here)

[/td]

[/tr]

[tr]

[td]

NA

[/td]

[td]

H1211800

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

14014555

[/td]

[td]

H117GG10

[/td]

[td]

75000

[/td]

[/tr]

[tr]

[td]

14012563

[/td]

[td]

H1211L30

[/td]

[td]

15000

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

See Detail

[/td]

[td]

See Detail

[/td]

[td]

**2** (formula to go here)

[/td]

[/tr]

[tr]

[td]

14014286

[/td]

[td]

H1177SLZ

[/td]

[td]

42635

[/td]

[/tr]

[tr]

[td]

14014289

[/td]

[td]

H1177SLZ

[/td]

[td]

22925

[/td]

[/tr]

[tr]

[td]

14014288

[/td]

[td]

H1177SLZ

[/td]

[td]

6925

[/td]

[/tr]

[/TABLE]

Ideally, I can have a simple formula with =IF(A2="See Detail",Sumproduct(This is where I need help),Sumproduct(--(Payments!DO=A2),--(Payments!JobNum=B2),Payments!Amount))

For the TRUE portion of the IF, I can set the dynamic range to be A3:A5 without issue. I just can't figure out how to make SUMIFS or SUMPRODUCT work with Payments!DO=A3:A5. It always wants to sum A3:A5, and I'm not sure how to tell it those are separate values.

Yes, it's all a bit convoluted, but the spreadsheet design is one I'm forced to use without changes, and the data sets are large enough that a one size fits all solution would be much better for me.

Cross posted here: http://www.excelforum.com/exce…g-to-nest-sumproduct.html