Count multiple column of "Text" in a date range

Kidmon said:

I need to create a formula that will (count "text" from 2 columns on sheet 1 within a specific date range) and show the totals in a summary I have on sheet 2.
I think it is something like this but I am missing 1 column of "text", =COUNTIFS('sheet1'!$D:$D,"text",'sheet1'!$G:$G,">="&"1/1/2011"+0,'sheet1'!$G:$G,"<="&"1/31/2011"+0)


This is what I need to add to the above formula. ('sheet1'!$D:$D,"text"),COUNTIF('sheet1'!$E:$E,"*text*"))

thanx for the help, luvin this forum.

Click to expand...

Those date criteria are really nasty!

Why don't you use cells to hold the date boundaries?

A1 = 1/1/2011
B1 = 1/31/2011

Maybe this is what you had in mind:

=COUNTIFS(Sheet1!$D:$D,"text",Sheet1!$D:$D,"*text*",Sheet1!$G:$G,">="&A1,Sheet1!$G:$G,"<="&B1)

Nasty is very true.http://www.mrexcel.com/forum/images/smilies/rolleyes.gif I thought about what you suggest with date=B:2 but I am not sure how to place the date reference on sheet 1, it is already almost too wide for easy viewing. I like your simple approach but how can I use date= from sheet 1 and summarize the info on sheet 2.
Thanx again

Kidmon said:

Nasty is very true.http://www.mrexcel.com/forum/images/smilies/rolleyes.gif I thought about what you suggest with date=B:2 but I am not sure how to place the date reference on sheet 1, it is already almost too wide for easy viewing. I like your simple approach but how can I use date= from sheet 1 and summarize the info on sheet 2.
Thanx again

Click to expand...

I'm not sure what you're asking.

Typically, if you have a summary sheet where you do all your calculations somewhere you have column/row labels that describe or refer to the calculation in a cell.

So, on your summary sheet where you want this formula use a couple of cells to hold the date boundaries.

If those cells are on a different sheet then just include the sheet name in the cell reference:

=COUNTIFS(Sheet1!$D:$D,"text",Sheet1!$D:$D,"*text*",Sheet1!$G:$G,">="&Sheet2!A1,Sheet1!$G:$G,"<="&Sheet2!B1)