CountIf with conditions

[professor.x]

Hi all,

I'm hoping someone here can help me solve this formula if it is at all possible. Here's the short story of the problem:

Columns and their representation:
Columns A through C represents an overview of data collected.
A = Names
B = Errors YTD
C = Errors MTD

Columns D through H are used for data collection.
D through F = Misc
G = Date
H = Names


I want to make Column B CountIF Column A is the equal to Column H and only if Column G is equal to or less than 12 months to today's date. A similar equation needs to be done with Column C to CountIF Column A is equal to Column H and only if Column G is equal to or less than 30 days to today's date.

Any help would be greatly appreciated.

 

[jmthompson]

What version of Excel are you running?

 

[jmthompson]

And the date comparisons, would column g be before today's date, after today's date or either?

 

[professor.x]

I am using Microsoft Office 2007. Column G would represent dates equal to today or previous dates.

 

[Excelestial]

Enter in CELL B1 and drag down:
=IF(OR(G1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

Enter in Cell C1 and drag down:
=IF(OR(G1<DATE(YEAR(TODAY()),MONTH(TODAY()-30),DAY(TODAY())),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

adjust as needed.

 

[Excelestial]

enter In cell B1 and drag down
=if(or(G1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

enter in cell c1 and drag down:
=if(or(G1<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-30),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

adjust as needed

 

[Excelestial]

enter In cell B1 and drag down
=if(or(G1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

 

[Excelestial]

wow...can't get the full formula to post...disregard the two prior truncated posts!

 

[Excelestial]

<TABLE style="WIDTH: 374pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=499 border=0><COLGROUP><COL style="WIDTH: 374pt; mso-width-source: userset; mso-width-alt: 18249" width=499><TBODY><TR style="HEIGHT: 135pt" height=180><TD class=xl63 id=td_post_2839394 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 374pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 135pt; BACKGROUND-COLOR: transparent" width=499 height=180>enter In cell B1 and drag down
=if(or(G1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)

enter in cell c1 and drag down:
=if(or(G1<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-30),G1>TODAY()),"",COUNTIF($A$1:$A$100,$H1)
</TD></TR></TBODY></TABLE>

 

[Excelestial]

=if(or(G1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())G1>TODAY()),"",
COUNTIF($A$1:$A$100,$H1)