# Complicated Sumifs formula?

I have 3 separate excel sheets-

(1) a list of invoices with columns: Invoice Number, customer ID, customer Name, and Amount Due.

(2) a list of products of a certain type- it has columns: invoice number, and date on it (no amount due or customer identification)

(3) a customer listing with various monitoring items that are filled in primarily from sheet (1)

I would like to add a monitoring item/column in sheet (3) that looks, by customer, for the \$ amount of invoices on list (2) older than 30 days How would I go about this in excel?

So- for example, if I had on sheet (1)

``````invNo    custName    custNo   amt
0001     ABC Co      C1234    \$20
0002     ABC Co      C1234    \$15
0003     ABC Co      C1234    \$50
``````

And on sheet (2) I had two entries (one less than 30 days and one over 30 days)

``````invNo    prodDate
0001     9/12/2019
0003     4/4/2018
``````

I'd simply like a formula to get sheet (3) to fill in productInvoicesOver30 and productInvoices (total of invoices on sheet (2))

``````custNo    productInvoices    productInvoicesOver30
C1234           \$70                    \$50
C5678           \$0                     \$0
``````

There may be items on sheet (2) with no date at all

I'm a bit stumped trying to find a way to do this that doesn't require altering sheets (1) or (2), so any help would be greatly appreciated!

Wrap the SUMIFS in SUMPRODUCT to iterate the ranges on Sheet(2):

``````=SUMPRODUCT(SUMIFS(D:D,A:A,\$G\$2:\$G\$3,C:C,J2))
``````

And for the older 30 days add a Boolean that checks the dates:

``````=SUMPRODUCT(SUMIFS(D:D,A:A,\$G\$2:\$G\$3,C:C,J2)*(\$H\$2:\$H\$3<TODAY()-30))
``````

• This works correctly, thank you! Unfortunately due to the amount of customers and invoices, my computer freezes when i apply the formula. It looks like I may need to find another way to do this. – Martin Goni Sep 16 '19 at 17:39
• @MartinGoni then you will want to investigate using VBA to do the whole in at once. – Scott Craner Sep 16 '19 at 18:39