Sunday, November 6, 2011

yogi_Compute The Number Of Times A Specified Entity Occurs In A Column And Present In The Specified Fashion

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user blakers81 said:
I am trying to devise a formula for my spreadsheet, but I'm stuck. I'm hoping someone can help me out. There are 3 columns. Column A has certain dates I enter manually. Column B is a formula, which extracts the months from Column A (and I've tested and it works). It looks like this: B1=MONTH(A1), etc. Now, I run into my problem with Column C. I am trying to make Column C get a tally for how many times a month shows up, but I only want one final count for each month. Another words, I need a formula for Column C, that will give the following results:
A B C
1 10/2/2011   10
2 10/30/2011  10  2
3 11/3/2011   11  1
4 12/2/2011   12
5 12/6/2011   12
6 12/15/2011  12  3
7 1/5/2012     1  1
I came up with: C1=IF(B1=B2," ",???)
The TRUE condition, which is to leave the cell blank, is right and works like it should. I just can't get the FALSE condition to work no matter what I try out.
Can anyone come up with a formula that will make the Column C cells look like the above? Thanks a lot for any help!
Blake
-----------------------------

following is one solution to the problem