# 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)

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.