Counting results using SUMPRODUCT

Andrew Barbaro

New Member
Joined
Feb 23, 2004
Messages
15
Code:
=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!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Can you not use =countif(H1:H25000>=.01) so if there is a balance over this amount it will count the cell
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top