If you have a non-numeric value in G3:g80, then =month() and =year() will return
errors.

Remember to unhide any rows (is autofilter on?).

You may want to try an alternative:

Instead of:

=SUMPRODUCT(--('Title VII Audits'!F3:F80="Yes"),
--(MONTH('Title VII Audits'!G3:G80)=6),
--('Title VII Audits'!G3:G80<""),
--(YEAR('Title VII Audits'!G3:G80)=2008))

try:

=SUMPRODUCT(--('Title VII Audits'!F3:F80="Yes"),
--(text('Title VII Audits'!G3:G80,"yyyymm")="200806"))

You'll find that =text() is more forgiving than =month() or =year().

On the other hand, if you actually have the string 200806 in G3:G80, then your
count won't be correct.


Cheese_whiz wrote:

How irritating.....finger spasm before post finished.

Anyway, so on the summary sheet the sumproduct formulas worked as expected.
The summary sheet rows had a month name (ex: january), a number for one
sumproduct based on two columns on the other worksheet (ex: 5), and a numer
for another sumproduct based on two other columns in the second worksheet.

The sumproducts are the EXACT same except for the columns they use in the
other worksheet.

Bearing in mind that they both worked before the columns were removed by the
user, and that both still point to the column pairs they are suppose to point
to, and that I've checked the data types in all the columns.....why
would/could one of them work now and the other show ?value?

The formula used is:

=SUMPRODUCT(--('Title VII Audits'!F3:F80="Yes"),--(MONTH('Title VII
Audits'!G3:G80)=6),--('Title VII Audits'!G3:G80<""),--(YEAR('Title VII
Audits'!G3:G80)=2008))

That's from a cell in the column on the summary sheet that is NOT working,
if it matters....

Tahnks for any help,
CW

"Cheese_whiz" wrote:

Hi all,

I made a spreadsheet and it was perfect. However, silly user didn't see it
that way and removed a couple of columns she deemed unnecessary.

On another worksheet I had some summary data, including 12 rows representing
each month of the year, and two other columns. Each of these two columns
used sumproduct to test the data in two columns on the worksheet where the
user removed the columns. If various conditions were met in the
corresponding two columns for the column/row on the summary worksheet, a '1'
was assigned and then those were added up by month.





--

Dave Peterson