Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was making a histogram today, and this pc doesn't have the Analysis Toolkit and for whatever reason I didn't feel like tracking it down and installing it.

So I found myself with a column of raw data and a column of bin values to use, and I wrote this:

 =COUNTIFS($A$1:$A$10,CONCATENATE(">=", $B1),$A$1:$A$10,CONCATENATE("<", $B2))

In other words, count the cells in my raw data range if they are above the lower bin and below the next one.

This did not work - every count returns zero.

If, instead, I pulled the concat into its own column (so Bins are in B and I have in C : CONCATENATE(">=", B1) and in column D I have CONCATENATE("<",B1) and then edit the histogram formula to reference the new intermediates, the Countifs works again.

 =COUNTIFS($A$1:$A$10, C1 ,$A$1:$A$10, D2)

I feel like there is some lesson about how the Excel formula parser is working under the hood in this that I should have internalized so it doesn't sneak up and kill me one day. Does anyone understand what's actually going on here?


Ok, well, Excel wants to make a monkey of me. Now this is working .. and I don't know of anything I did to make it switch from not-working (Which, I swear it was) to working. Maybe asking a question on the internet has some Spooky Action At a Distance type property. I suppose its possible that the cells were confused about being text as left over from the text to columns call and eventually they realized they were numbers? I'm baffled. Sorry for the goose chase!

share|improve this question

closed as too localized by slhck Feb 19 '13 at 14:32

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Are your raw data strings? Because your formula appears to work with numeric data. – chuff Feb 18 '13 at 22:11
I populate range A1:A10 with ordinal numbers 1,2,...,9,10 and in B1 put "5" and in B2 put "7", all stored as numbers. Your original formula works for me and does not return a 0 value ( Are you sure you have calculation automatic in the affected workbook file, or maybe it was inadvertently set to manual so what you are seeing is simply a formula which has not (recently) been re-evaluated? – David Zemens Feb 18 '13 at 22:13
They are numbers as far as I can tell - though they are the results of a text to columns call; I am going to try with raw data in a new sheet to see if I can reproduce it – Mikeb Feb 18 '13 at 22:18

Well it isn't a proper answer, but it seems to be "if you observe this behavior, just ask someone else and then Excel will make a fool of you and fix itself".

If I ever do understand what the heck happened I will try to come back and update this answer / question appropriately.

share|improve this answer
I've closed the question as too localized now (which we always do when issues magically resolved themselves). If you happen to find the actual solution, flag for moderator attention to have it reopened. Cheers. – slhck Feb 19 '13 at 14:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.