Counting with a Date Range

[Kidmon]

(using Excel 2010 Home)
From sheet 1, I am trying to count "Text" from multiple columns within a "Specific" date range and place the results on Sheet2.

Something like:

=COUNTIFS(Sheet1!$D:$D,"text",'sheet1!'!$G:$G,">="&"1/1/2011"+0,'sheet1!'!$G:$G,"<="&"1/31/2011"+0)

but I need to add Sheet1!E:E"*text*" but I can't seem to get my , or "" or () placed correctly.

How can I add another column to the above formula?
Thanx

 

[T. Valko]

(using Excel 2010 Home)
From sheet 1, I am trying to count "Text" from multiple columns within a "Specific" date range and place the results on Sheet2.

Something like:

=COUNTIFS(Sheet1!$D:$D,"text",'sheet1!'!$G:$G,">="&"1/1/2011"+0,'sheet1!'!$G:$G,"<="&"1/31/2011"+0)

but I need to add Sheet1!E:E"*text*" but I can't seem to get my , or "" or () placed correctly.

How can I add another column to the above formula?
Thanx

Didn't we get this sorted out in your other thread?

 

[Kidmon]

MY Bad, Yes, Sorted most of it. I forgot to mention the Date sorting part of my problem. I still need to know how to sort 2 columns with a specific date range criteria.
Feeling real silly on this end,
I'm not worthy.

 

[Aladin Akyurek]

MY Bad, Yes, Sorted most of it. I forgot to mention the Date sorting part of my problem. I still need to know how to sort 2 columns with a specific date range criteria.
Feeling real silly on this end,
I'm not worthy.

The following counts the occurrences of a string (text) like FAD in a range in columns D:E while the range shows dates between 1/1/2011 and 1/31/2011...

=SUM(IF(Sheet1!$G$2:$G$100>="1/1/2011"+0,IF(Sheet1!$G$2:$G$100<="1/31/2011"+0,IF(Sheet1!$D$2:$E$100="FAD",1))))

which must be confirmed with control+shift+enter, not just enter.

If text/string like FAD might occur as part of an entry in the range from D:E...

=SUM(IF(Sheet1!$G$2:$G$100>="1/1/2011"+0,IF(Sheet1!$G$2:$G$100<="1/31/2011"+0,IF(ISNUMBER(SEARCH("FAD",Sheet1!$D$2:$E$100)),1))))

which also must be confirmed with control+shift+enter, not just enter.

 

[T. Valko]

http://www.mrexcel.com/forum/showthread.php?t=533818

 

[Kidmon]

Thank you #NAME?. Right on the money. Your instruction of Control-shift-enter is what I was missing. Not sure what confirming does but it helped me.
Thanx again, VERY much appreciated.
Kidmon

 

[Kidmon]

OOPS, That was for you Aladin Thank you

 

[Kidmon]

Thanx for your previous help,
Here if the formula that works well HOWEVER, with the formula being used with a variety of "Text" on 12 separate ROWS My computer is slow to process everything. Do you have any suggestions on a way to speed this formula up?
Thanx again,
Kidmon (

=COUNTIFS('Sheet 1'!$D:$D,"1:1 Counseling",'Sheet 1'!$G:$G,">="&"1/1/2011"+0,'Sheet 1'!$G:$G,"<="&"1/31/2011"+0)

 

[Aladin Akyurek]

Thanx for your previous help,
Here if the formula that works well HOWEVER, with the formula being used with a variety of "Text" on 12 separate ROWS My computer is slow to process everything. Do you have any suggestions on a way to speed this formula up?
Thanx again,
Kidmon (

=COUNTIFS('Sheet 1'!$D:$D,"1:1 Counseling",'Sheet 1'!$G:$G,">="&"1/1/2011"+0,'Sheet 1'!$G:$G,"<="&"1/31/2011"+0)

The formula with COUNTIFS can be made to apply to a way smaller range instead of whole columns...

Have you considered to turn your current data area into a table and to feed COUNTIFS with the current ranges from the table?