EXCEL help needed!

[Oifish]

What I'm trying to do is calculate the total number of products launched ontime for a certain month. Its setup like:

Product............Planned Launch Date.....................Ontime?
A.....................MAR-07........................................x
B.....................MAR-07
C.....................APR-07........................................x
D.....................MAY-07........................................x

Thats just a crappy mockup but you get the point. I need a formula that will look at all the products with a launch date of MAR-07 and have an 'x' marked in the ontime column. Access would be a much better program to use for this idea but all the lazy f*cks here are afraid to use it. Help ASAP would be greatly appreciated. Thanks!

EDIT: I tried using the SUMPRODUCT, and COUNTIF formulas but I can't seem to get them to work and SUMPRODCUT is for numbers only.

 

[wayliff]

=IF(CELL="MAR-07", "X", "")
Then copy it all the way down?

Change "cell" for the actual cell

 

[wayliff]

then use this to count all the X's
=COUNTIF(RANGE, "X")

Change range for the actual range where the X's are

 

[davenyl]

=COUNTIF(B2:B5,"MAR-07")-COUNTIF(C2:C5,"<>X")

Just realized that won't work when there are different months. oh well.

 

[AgentDib]

I tackle something very similar to this with a straightforward VB macro. I'll paste a real code snippet when I get back to my system tomorrow, but here's some pseudocode:

Dim Rows as Variant
Dim TotalCount as Variant
totalcount = 0
- Start Loop: Step through each row variable (Rows) in column B, starting with the last filled in row and incrementing -1, then stopping at row 2 (if you have a header row).
---- If Cells(B,Rows).value = "Mar-07" then
---------- If Cells(C,Rows).value = "x" then
-------------------- TotalCount = TotalCount + 1
-----------End If
----- End If
- End Loop
MsgBox("We launched " & TotalCount & " products on time this month!"

You would then increase the flexibility by capturing the value of the selected cells content as the month variable so the user can count any month simply by clicking on a square from that month and then running the macro.

 

[Oifish]

I got it! I decided to not mess with text and convert all the 'x' to 1. Then used the DATEVALUE function to turn the dates into numbers. Then used this formula: =SUMPRODUCT(('CD Launch Metric'!D470=DATEVALUE("6/1/2007"))*('CD Launch Metric'!J4:J70=1))