
Current Special! Complete Excel
Excel
Training Course
for Excel 97  Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
See Also: Array Formulas  Count with multiple criteria  Sum with multiple criteria  Conditional date summing  Sum Every Nth Cell  Sum The x Largest or x Smallest Number in a Range  Count Only One Occurrence
Conditional Summing / Counting
A Forum Member needed to count based on conditions as follows
Column A  Column B 
01  0.5 
01  0.7 
01  1.0 
02  0.3 
02  2.0 
The result needed was as follows:
If Column = 01 then count how many cells in Column B are less than 1
XLDennis provided a solution using SUMPRODUCT
=SUMPRODUCT((A1:A5="01")*(B1:B5<1))
Another poster required further explanation of how
SUMPRODUCT worked, and
this was explained in great detail by Chris Davison.
Let’s look at the first part of the SUMPRODUCT solution: (A1:A5="01")
(A1:A5="01") is a Boolean statement – i.e. the statement is either
TRUE
or FALSE for each value – in the above example…
TRUE
TRUE
TRUE
FALSE
FALSE
or, since TRUE=1 and FALSE=0 :
1
1
1
0
0
looking at the second part :
(B1:B5<1)
Another Boolean test  the statement is either TRUE or FALSE:
TRUE
TRUE
FALSE
TRUE
FALSE
and again, since TRUE=1 and FALSE=0 :
1
1
0
1
0
since there are two conditions, SUMPRODUCT multiplies the products of both
criteria in this array…
1x1
1x1
1x0
0x1
0x0
equals
1
1
0
0
0
The PRODUCT of the results :
1+1+0+0+0 = 2
So the net result is 2 (i.e. 2 conditions conform to the criteria we specified)
The formula can be used in many ways and once you discover SUMPRODUCT, it seems
like a gift from heaven – a word of caution though…
Imagine having 10 criteria (i.e. names that begin with "B", in the "North",
where the value is less than $5000, with a date in January, for salesman "Bloggs",
for company "ABC Corp" and you're interrogating 5,000 records....
SUMPRODUCT will interrogate each record (all 5,000 of them) ten separate times,
to ascertain whether each criteria is in fact TRUE or FALSE.... then when it's
got all these, it multiplies them all together 5,000 x 10 calculations, then
adds up the results.... with such a query, I wouldn't be surprised if it took a
minute or so to calculate, so while it's useful, if you begin to notice
performance degradations on your worksheets as you use them more often, you may
be better off looking at the range of Database formulae..( DSUM, DGET, DCOUNTA
etc) which basically do the same thing but are much easier to set up and
maintain, and, from experience, are a lot quicker.,
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates