I currently produce a report that takes CSV input showing the current state of a DB and does a simple tally to arrive at applicable charges.
I am embarrassed to admit that I built in a silly kludge to get the result I wanted, more than a year ago.
Each client’s line on the report uses this formula to arrive at their tally:
=SUMIF(item_list!J:J,"=CLIENT",item_list!L:L)
The thing is, although every line in the item list represents a single item, I don’t have control over the generated list, and it does not conveniently have a “1” at the end of each line to be summed - so every month I cut & paste a column of 1500 or so ones into column ‘L’ to make it work, and my ears burn a little bit each time I think about the possibility of someone looking closely enough to discover this dirty secret. :o
Can anyone suggest a less-stupid way of simply counting the instances of a value appearing in a column?
Yeah, it’s COUNTIF. Like if I had a list of 1,000 rows where the variable is, say, one of ten names, I’d use =CountIf(A:A, “David”) and it would count all the cells that have “David” in it.
Of course, this is nearly as embarrassing as being caught out with my wrong-way-around approach. (It preserves the illusion of my super-genius status at work, though, and for that I am very grateful.)
You can always use a pivot table if the data needs to be counted on a regular basis and every variable is important, like if you need to count how many sales each employee made in a week. It’s easier than making 10 or 20 countif statements.
Warning: once you figure out how pivot tables work, you will immediately start trying to properly reformat every spreadsheet in the building so it will be pivot-friendly.