• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif Formula Help

E

ELW1976

New Member
I am trying to create a formula in excel where i can track the date someone purchased a subscription vs. the date the subscription was activated. I am having trouble using the COUNTIF and SUMPRODUCT formulas because I want to see the data between date ranges. For example i have 10 subscriptions purchased all on 1/1/2011 (column A lists unique subscription ID, Column B lists date purchased, Column C lists date activated). For the first 10 subscriptions, 4 were activated on 1/1/11, 1 on 2/10/11, 1 on 2/15/11 and 1 on 2/28/11 and 3 on 6/30/11). I want to see how many were activated within January (4)February (3) and so on, but can't get the formula to spit out the conditions. Can anyone help with this?
 
It depends on what you'd like the display to look like. There are different presentations that could work. Something like:


=SUMPRODUCT((TEXT(B2:B11,"MMM")=E1)*1)


with the three letter abbreviation for the month of interest in cell E1 works. You can make E1 a drop-down with the twelve months if you just want to spot check. If you'd like to specifically look at Jan-2011 you can change the format string to "MMMYY" and include the year in the drop-down strings.


Alternatively, a pivot table would present everything in a table and be easy to set up / maintain.
 
I am fairly new to excel. I am trying to count certain cells which contain a specific word as well as a specific color. I cannot find any one able to help with this. I am sure it is easy, but like I said, I am new at this... It's incredibly urgent as well, as I am trying to compile a report for my customers.


Thanks for any and all help!
 
can you give an example to "count certain cells which contain a specific word as well as a specific color"?


Mike86 already give you the answer to the count. If you want color based on "Jan", a different color base on "Feb", etc. you can use conditional formatting.
 
I have a format to count cells with specific data inside, and I have a format to count color filled cells. Here's exactly what I am needing to count: All cells colored pink which contain the word "compass."


Does this help you help me?


Cheers!
 
go to conditional formatting; format only cells that contains the word "compass". set the color to pink. Hit the enter key.
 
at the risk of truly reveal the computer illiterate moi, would you mind sharing where the conditional formatting is? Or rather what the formula might look like? Example: =countif(blah blah blah...etc)


I am very wet behind the ears here...
 
Okay I found the conditional formatting... but looking at the little prompt boxes... heavy sigh... between my ears there is an echo... so embarrassing... sheesh
 
Well under conditional formatting, I cannot figure out how to include the word "compass" as a data point. I managed to turn the cell where I want the total to be, Pink... but that's it... I get a pink square with no data inside it.


Sorry for being such a pain.
 
Tagless

If you have Excel 2010 you can select the data range

Right Click and Filter by Selected Cells Color

The Status Bar will then show you the Count and Sum of the filtered data
 
You must log in or register to reply here.
Back
Top