Countifs, from multiple columns, within range of years

[anonymous8472]

I have two columns from worksheet Deals!, T and U marked with x's, I need to count each time x appears within these rows, but only in each year.


Below formula only counts rows where x appears both time per row.


=SUM(COUNTIFS(Deals!$T$2:$T$500,"x",Deals!$U$2:$U$500,"x", Deals!$C$2:$C$500,">=1/1/2005",Deals!$C$2:$C$500,"<=1/1/2006"))


I continue to be baffled by the concept of arrays, which I'm sure are applicable here.


Thanks for any help...

 

[Luke M]

New formula (don't enter as an array)

=SUMPRODUCT(((Deals!$T$2:$T$500="x")+(Deals!$U$2:$U$500="x")>0)*

(YEAR(Deals!$C$2:$C$500)=2005))


First part checks col T and U for a "x". If it exists in either one (checked via the ">0") then we check col C to see if the year is correct. If the year is correct, it gets added to the sum.


Just to clarify, I was assuming that this group of x's:

[pre]

x   x
x
x

[/pre]
would be a count of 3, assuming the dates were valid.

 

[anonymous8472]

Yes, correct Luke...thanks muchly. How would I replicate this for month? Assuming mm/dd/yyyy format. Replace YEAR with MONTH?


I really appreciate the help you guys give on here.


On another forum, I was advised to start using pivot tables for situations like this. Is it worth mastering?

 

[SirJB7]

@Luke M

Hi!

That leaves out the 01/01/2006 which should be included.

Regards!


Hi, xenomorph8472!

In your SUM formula there's only one element, the COUNTIFS formula, so SUM isn't necessary, just keep the COUNTIFS.

Regards!

 

[anonymous8472]

Luke formula is better than mine, I actually don't need 1/1/2006....

 

[Luke M]

Hi xenomorph!


To give you more flexibility, I'll change the YEAR function to a TEXT function.

=SUMPRODUCT(((Deals!$T$2:$T$500="x")+(Deals!$U$2:$U$500="x")>0)*

(TEXT(Deals!$C$2:$C$500,"mmyyyy")="032005"))


Formula has the advantage of being able to handle erroneous text entries, and you can change the format to look for a specific year/month.