Count IF multiple criteria

[Sarah Barah]

I hope someone can PLEASE help me

How do you get a countif to work within a countif's results? I want it to
count how many cells within a range contain certain text and within the
results of that I want it to tell me how many fall within a range of dates.
This is to be reported on a seperate spreadsheet within the workbook so
filtering isn't an option.

I thought the below formula would work but it actually adds the count of
cells that contain the text "yhard" with the count of cells that fall within
the date range.

=COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January-
STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008")))

Is there a way to connect an AND and have the count ifs done in sequential
order by connecting the following 2 formulas?
#1 criteria =COUNTIF('January- STD'!$C$7:$C269,"*yhard*")
# 2 criteria =COUNTIF('January-
STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF('January-
STD'!$G$9:$G$155,"<01/01/2008")

Thank you thank you thank you in advance if there is a genious out there
that can help me solve my problem!

 

[Pete_UK]

COUNTIF is limited to a single condition - if you have more, then you
can use SUMPRODUCT like this:

=SUMPRODUCT((ISNUMBER(SEARCH("yhard",'January- STD'!$C$7:$C269)))*
('January-
STD'!$G$7:$G$269<=--"01/30/2008")*('January-
STD'!$G$7:$G$269>--"01/01/2008"))

Hope this helps.

Pete

 

[PCLIVE]

Sarah,

You might try using SUMPRODUCT. However, there are a few things to
consider. SUMPRODUCT doesn't handle the wide card (*) the way you have your
formula written. If you can do without that, then:

=SUMPRODUCT(--('January- STD'!$C$7:$C269="yhard"),--('January-
STD'!$G$7:$G$269<=M1),--('January- STD'!$G$7:$G$269>=L1))

Also, your ranges didn't match up. SUMPRODUCT will require matching ranges
as I have done above. This will also work better for you if you select two
cells and have the start and end dates in them. For example, my formula
assums that L1 contains the Start date and M1 contains the End date.

Lastly, you may have intended it to be this way, but I wanted to point out
that your formula was only attempting to calculate through January 30th, and
January has 31 days.

Hope this helps,
Paul

 

[T. Valko]

That formula won't work but you have a problem before I can make a
suggestion that will work: the ranges should (read: have to) be the same
size:

$C$7:$C269
$G$9:$G$155

 

[Sean Timmons]

What you're looking for there is a sumproduct.

=COUNTIF('January- STD'!$C$7:$C269,"*yhard*")+((COUNTIF('January-

STD'!$G$9:$G$155,"<=01/30/2008")-COUNTIF($G$9:$G$155,"<01/01/2008")))

=SUMPRODUCT(--('January- STD'!$C$7:$C269="*yhard*"),--('January-
STD'!$G$7:$G$269<=01/30/2008),--('January- STD'!$G$7:$G$269>12/31/2007))

Will count all rows where the string yhard is found within column C and the
date in column g is from January 2008. Please note.... All ranges must be
equal, thus 7 - 269 in all 3 pieces. the -- makes each value either a 0 or a
1 based on if the cell matches the criterion. In the above, you MUST use a
range. Don't try using C:C, must be C7-C269 or some such...

HTH!

 

[Pete_UK]

Sean,

a few things wrong with your suggested formula - you can't use
wildcards like that, and your first "date" will be evaluated as 1
divided by 30 divided by 2008, so it's not going to work.

Pete

 

[Sarah Barah]

Thank you Pete from the UK! You are the winning genious! Your below
formula works... I dont really understand the ISNUMBER and SEARCH in terms of
how but as long as I get the correct answer I am happy. This saves me hours
upon hours of work. Thanks again!

 

[Pete_UK]

You're welcome, Sarah - thanks for your kind words, though I didn't know it
was a competition !! <bg>

The ISNUMBER(SEARCH(... construct is needed because we can't use wildcards
in this formula, but basically it means that the cell should be counted if
the string "yhard" is contained in the cell.

The asterisks are equivalent to AND, so you could express the formula as
meaning "Using rows 7 to 269, count all the cells where "yhard" is contained
in column C AND the date is after 1st Jan 2008 in column G AND the date is
before 31st Jan 2008 in column G". Note that this does not include 1st Jan
2008 - you can change the > to >= if you want to include that date.

Pete