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!

0

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.