<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**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

**XL-Dennis **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 Add-ins Collection on all purchases totaling over $64.00. ALLpurchases 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...**