Counting results using SUMPRODUCT

[Andrew Barbaro]

=SUMPRODUCT((Ledger!$L$1:$L$25000="D")*(Ledger!$M$1:$M$25000=SalesRefer!$A5)*(Ledger!$AA$1:$AA$25000=1)*(Ledger!$N$1:$N$25000=1)*(Ledger!$O$1:$O$25000=A1),(Ledger!$H$1:$H$25000))

I'm successfully using the above formula to list invoice balances that have been referred to sales people to collect. The references relates to whether or not they are overdue in column AA (1= overdue, anything else is not) etc.

I'd ideally like to be able to count the number of cells satisfying the above criteria that are contained in column H.

Any help gratefully received. Thanks!

 

[krisso]

Can you not use =countif(H1:H25000>=.01) so if there is a balance over this amount it will count the cell

 

[fairwinds]

Hi,

If you just remove the last reference in the formula, you should get the count:

=SUMPRODUCT((Ledger!$L$1:$L$25000="D")*(Ledger!$M$1:$M$25000=SalesRefer!$A5)*(Ledger!$AA$1:$AA$25000=1)*(Ledger!$N$1:$N$25000=1)*(Ledger!$O$1:$O$25000=A1))