Count IF multiple criteria
 

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! :)

Count IF multiple criteria
 

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

On Dec 11, 4:53*pm, Sarah Barah <Sarah
wrote:
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! :)

Count IF multiple criteria
 

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

--

"Sarah Barah" <Sarah wrote in message
...
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! :)

Count IF multiple criteria
 

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

--
Biff
Microsoft Excel MVP


"Sarah Barah" <Sarah wrote in message
...
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! :)

Count IF multiple criteria
 

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$26912/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!

"Sarah Barah" wrote:

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! :)

Count IF multiple criteria
 

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

On Dec 11, 5:43*pm, Sean Timmons
wrote:
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$26912/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!



"Sarah Barah" wrote:
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! :)- Hide quoted text -

- Show quoted text -

Count IF multiple criteria
 

:)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" wrote:

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

On Dec 11, 4:53 pm, Sarah Barah <Sarah
wrote:
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! :)

Count IF multiple criteria
 

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

"Sarah Barah" wrote in message
...
:)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" wrote:

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

On Dec 11, 4:53 pm, Sarah Barah <Sarah
wrote:
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! :)