2

I got the below result using the formula

={query(SALES!A2:Y ,"Select B, C, D, G,  P,  Sum(T), datediff(now(), todate(P)) WHERE O ='"&(A1)&"' GROUP BY B, C, D, G, P ORDER BY datediff(now(), todate(P)) DESC Label Sum(T) 'PENDING AMOUNT', datediff(now(), todate(P)) 'OVER DAYS' " ,1)}

Now I need the last column status using if (overdue / underdue based on overdays) using the above formula. pl help

INV.DATE    INVOICE NUMBER  CUSTOMER NAME   DUE DATE    PENDING AMOUNT  OVER DAYS   STATUS
15-Apr-2020 IBLR001-2021    abc ltd        15-May-2020  ₹2,46,750       93          OVER DUE
21-Apr-2020 IBLR002-2021    xyx            20-Aug-2020  ₹5,87,788       -4          UNDER DUE
marikamitsos
  • 10,264
  • 20
  • 26
Narahari GL
  • 21
  • 2

1 Answers1

1

Please use the following formula for your STATUS column

=ArrayFormula(IF((F2:F=0),"",IF(F2:F>0,"OVER DUE","UNDER DUE")))

where F is your OVER DAYS column. You can adjust the ranges according to your needs.

enter image description here

Functions used:

marikamitsos
  • 10,264
  • 20
  • 26