0

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!

1 Answer 1

1

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))

enter image description here

2
  • 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. Sep 16, 2019 at 17:39
  • @MartinGoni then you will want to investigate using VBA to do the whole in at once. Sep 16, 2019 at 18:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .