I know I can't use COUNTIF with multiple conditions

[scorpia]

I am trying to get a count of the number of rows in a worksheet that meet the following conditions:

1. Date in a specified cell is greater than today's date - ie:
(BD$35<TODAY())<TODAY()< font>
2. A specifed range of rows contains the word/characters TBD - ie:
=--ISANUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)) OR (Sheet2'!D11:D388,”*TBD*”)
3. A specified range of rows contaings the word/characters COO as the exact value in a colum - ie: ('Sheet2'!$BI11:$BI388=COO")

I know this is confusing without sample data - I will try and make a simple exampe. In the mean time I just need to be pointed in the right direction - basically if the value for a specific column heading is greater than today's date I want to know the number of rows where TBD appears in Column D on Sheet 2 and where Column BI on Sheet 2 is equal to COO.

Thanks!

 

[c_m_s_jr]

You've done most of the hard work by coming up with the other formulas

Maybe something like:

IF(COLUMNHEADER>BD$35,SUMPRODUCT(--ISANUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)), --('Sheet2'!$BI11:$BI388=COO")),"")

 

[scorpia]

I tried this, based on your post:


IF(BD$35<TODAY()),SUMPRODUCT(--ISANUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)), --('Sheet 2'!$BI11:$BI388=COO")),"")
Doesn't work - you had me up to the TBD condition. After that I couldn't follow it. Any other suggestions?!?

 

[lenze]

Did you correct the typo in j_m_s_jr's formula/

IF(COLUMNHEADER>BD$35,SUMPRODUCT(--ISANUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)), --('Sheet2'!$BI11:$BI388="COO")),"")

You might also use Today()

IF(COLUMNHEADER>Today(),SUMPRODUCT(--ISANUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)), --('Sheet2'!$BI11:$BI388="COO")),"")

lenze

 

[brockk]

depending on the version of excel (ej: 2007) has a COUNTIFS option you can use. Not sure if prior versions of excel can do this.

 

[c_m_s_jr]

Thanks for the catch lenze.

As a clearification questions...

Wouldn't it be more efficient to only use the TODAY() function once 9BD35) since it is a volitle function rather than multiple times when the formula is copied and pasted into different cells?

 

[scorpia]

I was going to use Today() but for some reason it didn't show up in my post. However, let me clarify that row 35 is a week ending date header, it does not contain today's date. BD35 is a future week.

I had caught the typo.. I am getting the #NAME error.

 

[lenze]

I think there is another typo that neither j_m_s_jr or I caught

IF(COLUMNHEADER>BD$35,SUMPRODUCT(--ISNUMBER(SEARCH(“TBD”, 'Sheet2'!D11:D388)), --('Sheet2'!$BI11:$BI388="COO")),"")

Unless ISANUMBER is a function in 2007 or a UDF
lenze

 

[scorpia]

<P><SPAN style="FONT-FAMILY: 'Verdana','sans-serif'; COLOR: black; FONT-SIZE: 10pt">Below is a link to an image of a sample I made. The actual worksheet is huge and the appropriate data is on several tabs.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o></SPAN></P>
<P><SPAN style="FONT-FAMILY: 'Verdana','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> <o></o></SPAN></P>
<P><SPAN style="FONT-FAMILY: 'Verdana','sans-serif'; COLOR: black; FONT-SIZE: 10pt"><A href="http://imagecloset.com/tiny/864530">http://imagecloset.com/tiny/864530</A><o></o></SPAN></P>
<P><SPAN style="FONT-FAMILY: 'Verdana','sans-serif'; COLOR: black; FONT-SIZE: 10pt"> <o></o></SPAN></P>
<P style="MARGIN: 0in 0in 10pt" class=MsoNormal><FONT size=3 face=Calibri>Basically, I want to populate rows 10-12 on the sample with formulas. I need headcounts of everyone who has TBD in the employee name for col A and has a division of CCO in col B for that specific week – but only if the week in that col is greater than today’s date as I want headcounts for past weeks to disappear.</FONT></P>
<P> </P>

 

[scorpia]

So I now know I need to do a SUMPRODUCT nested in an IF(AND

The only part I am still struggling with is the wild card/find bit. I need to know the right way to do the IF(AND - If BD$35 greater than today() and the specified range includes the characters TBD. I would then use the SUMPRODUCT to count the cells that have COO in the specified range and also report hours worked. All of these components work individually except the find TBD part. Any suggestions?