# Silly Excel Q: Like SUMIF, but with a constant? **URL:** \ **Category:** Factual Questions\ **Created:** [December 6, 2010, 6:31pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173 "2010-12-06T18:31:24Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Larry\_Mudd](https://avatars.discourse-cdn.com/v4/letter/l/f14d63/32.png) [@Larry\_Mudd](https://boards.straightdope.com/u/Larry_Mudd)\ **Post date:** [December 6, 2010, 6:31pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/1 "2010-12-06T18:31:24Z")
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? Thanks! ---
**Author:** ![mallocks](https://avatars.discourse-cdn.com/v4/letter/m/f4b2a3/32.png) [@mallocks](https://boards.straightdope.com/u/mallocks)\ **Post date:** [December 6, 2010, 6:42pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/2 "2010-12-06T18:42:30Z")
I think COUNTIF might work here for you From what I understand of your document it would then be **=COUNTIF(item\_list!J:J,"=CLIENT")** ---
**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\ **Post date:** [December 6, 2010, 6:46pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/3 "2010-12-06T18:46:31Z")
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. ---
**Author:** ![Larry\_Mudd](https://avatars.discourse-cdn.com/v4/letter/l/f14d63/32.png) [@Larry\_Mudd](https://boards.straightdope.com/u/Larry_Mudd)\ **Post date:** [December 6, 2010, 6:51pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/4 "2010-12-06T18:51:56Z")
Beautiful, thank you. 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.) ---
**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\ **Post date:** [December 6, 2010, 6:59pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/5 "2010-12-06T18:59:00Z")
> [@Larry\_Mudd](#): > > Beautiful, thank you. > > 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. ---
**Author:** ![Larry\_Mudd](https://avatars.discourse-cdn.com/v4/letter/l/f14d63/32.png) [@Larry\_Mudd](https://boards.straightdope.com/u/Larry_Mudd)\ **Post date:** [December 6, 2010, 10:57pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/6 "2010-12-06T22:57:36Z")
> [@Chessic\_Sense](#): > > 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. That does indeed look handy - I think I have another application for that. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [December 6, 2010, 11:26pm UTC](https://boards.straightdope.com/t/silly-excel-q-like-sumif-but-with-a-constant/563173/7 "2010-12-06T23:26:52Z")
> [@Larry\_Mudd](#): > > That does indeed look handy - I think I have another application for that. 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.