count if range contains match to another cell

[Guest]

I have an array of data on 1 sheet that contains (among other things) a
straightforward date column and also a column in which some of the cells
contain the word "New". I would like to count the rows that have both the
word "New", and also a date which falls within the month found in a cell on
another sheet. But, the date on the other sheet is not in date format and
can't be, because it has to be the 3-letter abbreviation of the month in all
caps (i.e. FEB) in order for the formulas on that sheet to work.
Yikes! Thank you....
Ross

 

[cvolkert]

I think the formula below should do the trick for you counting issue.
In the formula, you need to change the following:

A2:a20 is your range of dates
G8 is the range where you would enter FEB
B2:b20 is the range that would have New in it

=SUMPRODUCT((TEXT($A$2:$A$20,"MMM")=G8)*($B$2:$B$20="New"))

Let me know if you have any problems with this - I tested it quickly
and it seemed to work. Later - Chad

 

[Guest]

Hmm...thanks Chad, seems like a good start, but...
When I use your formula as is, I get #######. Maybe (I thought) because my
date range (E7:E100) is in date format 1-Jan-06? Also, those date values, in
case it matters, are populated via a formula that references a totally
different document.
I changed it to this, but now get a #VALUE error:

=SUMPRODUCT((TEXT((MONTH($E$7:$E$100)),"MMM")=ExecSum!H7)*($F$7:$F$100="New"))
thinking that would convert the date range to their month code?

Do I change the "MMM"? I tried it to no avail. But if so, the idea is to
not have to change this formula - so when users change the month on the other
sheet (ExecSum!H7), they don't need to change the formula on this sheet.

also, just a stupid question that I should know - what do the dollar signs
mean?

Thanks!
Ross

 

[cvolkert]

I'm not sure why the first formula won't work on your end. I tried it
again and it worked on my end (I did have to delete the space between
the 2 & the 0 in the latter part of the formula. You definitely don't
want to convert to the month number (using the Month function) or you
will get January for anything you put in there. One thing you may want
to try is format the cell where the formula is as a number - if it is a
date or something else that may be causing the #### which typically
means the cell contents are too large for the cell.

As for the $ - they make a range absolute. For instance, if you key a
formula in without $ and then drag it down or to the side, the cell
references in the formula will move relative to where the formula is.
this is usually not something people want to have happen.

 

[Guest]

Oh! Yeah the cell was just formatted wrong! Well, that worked to make the
formula give a value instead of ####.
But...the value is 0 which isn't correct.
Currently, I for the formula I have:

=SUMPRODUCT((TEXT($E$7:$E$100,"MMM")=ExecSum!H7)*($F$7:$F$100="*New*"))

ExecSum!H7 contains the plain text value of FEB
There is one cell in E7:E100 that contains both a Feb date (1-Feb-06 to be
exact, and that is the product of a formula pointing to another document) AND
"New" within the value of it's cell in column F (not just 'New' though as
there's other stuff in that cell also, hence the asterisks). So, the value
should be 1, but it's 0.

So glad to know about the $. That would've saved me a lot of work. oh well
- now i know - thanks!
Ross

 

[cvolkert]

I didn't realize there would be other text other than just New in the
second range. Putting * between the quotes does not create wildcards
so that's why you were getting 0 - it was looking for the exact text
*New*. The formula below works around this. I don't know what other
text could be in this column, but note that words containing 'New' will
also return a positive. Perhaps unlikely, but something like Newton
would return a positive. Let me know if you need anything else.

=SUMPRODUCT((TEXT($E$7:$E$100,"MMM")=ExecSumm!H7)*(1-ISERROR(FIND("New",$F$7:$F$100))))

 

[Guest]

No, every instance of New in this case would be valid. I'm getting #REF!
though! No idea why. I tried messing with the FIND part, but nothings
working. Sorry for so much back and forth, but thanks so much for your help
with this.

 

[Guest]

Oh!!! Sorry, I figured it out. I just cut and pasted your new formula, but
I didn't notice the ExecSumm part had 2 m's, it's only supposed to have 1.
That worked!
Thanks again so much for your help
Ross