Data Manipulation

Conditional Summing and Counting

Very often, you’ll want to sum (or count) only certain items in a list.  For example, you may want to see if particular days are better for sales than others, to ensure sufficient staffing. SUMIF is the function that enables this task.  In the example to the right, we have daily data for 3 stores (Vancouver, Coquitlam and Calgary).  We also have the Year and Day of the Week identified for each daily revenue amount. To find out how much we sell by day of the week, we would first set up a matrix, with Day of the week on the left, and store along the top:
In cell P2, we enter this formula: = SUMIF(C10:C2200,O1,D10:D2200)
This first range holds the “lookup” or “condition” values; in our case, the days of the week. “01” contains the value (day of the week) that we want to find in C10:C2200. This range holds the values to be summed; in our case, the daily sales amounts in the Vancouver store.
The output of our formula, copied into each of the cells.  Of course, the Column references for Coquitlam are E10:E2200, and for Calgary, F10:F2200. So, a quick eyeball of the data shows that the poorest days are, from left to right, Sunday in Vancouver, Monday in Coquitlam, and Wednesday in Calgary.  Best days are Tuesday in Vancouver, Wednesday in Coquitlam, and Tuesday again in Calgary. Using another formula (see below), we can see that the worst days are only 7-8% less than the best days - not really enough of a difference to suggest differential staffing requirements. The formula we used in row 10 to compare the best to worst days is: = MIN (P2:P8) / MAX (P2:P8) MIN returns the MINimum amount in a range, and MAX returns the MAXimum amount.  Be careful, though - these are not absolute values!  Negative numbers are the reverse of positive numbers, so -296 (negative) is less than 1 (positive). MIN and MAX are very useful functions for understanding data sets and will become more useful when we look at automation through Macros.
The COUNTIF function is a close relative of the SUMIF.  For the same range, rather than adding up all the values, it counts how many values there are.  COUNTIF will count ANY matching values, strings or numbers. For our data set, the COUNTIF function would be:  = COUNTIF(C10:C2200,O1) This is the same as our SUMIF function, except we’ve taken off the last parameter, the “sum range”.  So, all of this looks at summing and counting ranges with ONE condition.  What about if you have two or more conditions that need to be satisfied?  Those functions are SUMIFS and COUNTIFS.
Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase My Account My Account Blog Blog