Using Countifs, on entries in multiple columns

[Befuddled]

In the attached workbook, I am trying to count the instances where there is an "x" is in one of the 2 cells next to an entry. between the dates given(month).
I have this formula:

=COUNTIFS('2010 PM'!$E$3:$E$193,"x",'2010 PM'!$D$3:$D$193,">="&'Spot PM count'!B4,'2010 PM'!$D$3:$D$193,"<="&'Spot PM count'!C4,'2010 PM'!$C$3:$C$193,"")+COUNTIFS('2010 PM'!$F$3:$F$193,"x",'2010 PM'!$D$3:$D$193,">="&'Spot PM count'!B4,'2010 PM'!$D$3:$D$193,"<="&'Spot PM count'!C4,'2010 PM'!$C$3:$C$193,"")

and it works, for 1 of the 15 columns I need to search. Is there another way to do this? Or am I going to have 15 subtotals per month, to get the info I need?

I am looking to automate the B5:M6 cells in the "Spot PM count" sheet. The handcounted numbers for this are in B19:M20 of the same sheet

Thanks for any insight you can give me on this problem

[NBVC]

Can you explain how you get 5 for May, 7 for June and 10 for July?

What exactly is getting counted?

[Befuddled]

For example, in '2010 PM'!Y5:AA5 there is an entry of 11/29/10 and an x.
For every date entry in '2010 PM'!D3:AV193, with a value between 11/1/10 and 11/30/10, I want to know how many "x"s there are in the 2 corresponding cells next to each of those entries. This would be the total for November.

If you look in sheet "2010 PM" column C, each row either has an x, or not. I need to break down the total from above, into the total with an "x" in column C, and the total without an "x" in column C.

So, in sheet "Spot PM Count" L19 I come up with an answer of 2 "x" entries in '2010 PM'!D3:AV193 that DO NOT have an "x" in column C
and in sheet "Spot PM Count" L20 I come up with an answer of 8 "x" entries in '2010 PM'!D3:AV193 that DO have an "x" in column C

[NBVC]

I come up with slightly differing numbers in some places...

Try in B5:

=SUMPRODUCT(('2010 PM'!$C$3:$C$193="x")*(TEXT('2010 PM'!$D$3:$AT$193,"mmmyy")=TEXT(B$4,"mmmyy"))*(('2010 PM'!$E$3:$AU$193="x")+('2010 PM'!$F$3:$AV$193="x")))

copied across

and in C5:

=SUMPRODUCT(('2010 PM'!$C$3:$C$193<>"x")*(TEXT('2010 PM'!$D$3:$AT$193,"mmmyy")=TEXT(B$4,"mmmyy"))*(('2010 PM'!$E$3:$AU$193="x")+('2010 PM'!$F$3:$AV$193="x")))

copied across

[Befuddled]

Thanks very much, this works great. I knew there had to be a more elegant, and less cumbersome, way to do this.

Any numbers that were different, must have been a miss-count from when I did it by hand

[Befuddled]

Just a question about that formula.
=SUMPRODUCT(('2010 PM'!$C$3:$C$193<>"x")*(TEXT('2010 PM'!$D$3:$AT$193,"mmmyy")=TEXT(B$4,"mmmyy"))*(('2010 PM'!$E$3:$AU$193="x")+('2010 PM'!$F$3:$AV$193="x")))

by using D3:AT193 for the text cells and using E3:AU193 for the 1st "x" and F3:AV193 for the 2nd "x", the formuls know to ONLY look in the 2 cells next to the entry that fits the text criteria, and the <>"x" criteria in column C?

[NBVC]

Have a read through this article: Sumproduct

Sumproduct is a versatile function.

It is a matter of just lining up the arrays so the results line up.

IF you notice the E3:AU and F3:AV3 are offset arrays from the D3:AT3... so what I am doing is lining up the left arrays so that the results are in the same relative position.