double Sum if criterias !

  • Thread starter Thread starter M Imran Buhary
  • Start date Start date
M

M Imran Buhary

I have a sales database with several columns. In one column I have customer
name (Unique ID). I have sales details of all invoices listed and some are
been paid and some are pending for payment. I want to extract in another
worksheet a total of pending invoices for each customer. I need to do this
without PIVOT Tables.

I have listed the Unique customer names in the other worksheet and try to
use sumif formular. But i have the problem to give 2 conditions one is to
match the Customer ID and also to look for pending invoices only.

Can anyone help on this one please !
 
How do you distinguish between pending and not pending?

let's say in columnA you have your ID
columnB is the amount
ColumnC is "pending" or "paid"

you could either make a new columnD where you put the
formula: =B1*(C1="pending")
and then do the sumif over this column

Or

you could try a array formula:
=SUM((Sheet1!$A$1:$A$13=Sheet2!A1)*Sheet1!$B$1:$B$13*(Sheet1!$C$1:$C
$13="pending"))
which you have to input with ctrl+shift+enter, not just enter.

hth
Carlo
 
[...]:
=SUM((Sheet1!$A$1:$A$13=Sheet2!A1)*Sheet1!$B$1:$B$13*(Sheet1!$C$1:$C
$13="pending"))
which you have to input with ctrl+shift+enter, not just enter.

hth
Carlo
Click to expand...

Or:

=SUMPRODUCT((A1:A10=IDCustomer)*(B1:B10="pending")*(C1:C10))

where:
A1:A10 ==> customer ID
B1:B10 ==> paid | pending
C1:C10 ==> invoice amount

Bruno
 
Back
Top