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.

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.

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

Hi Dave,

Thanks for the reply. I finally figured out what the deal was and I feel
kinda dumb for not rolling through and checking more closely. User, after
utilizing the file for a while, all the sudden changed the date format she
used in two different/random cells in the column that was part of the pair
that didn't work.

I guess I need to investigate Excel or vba validation solution for these
cells.

Thanks again, though.
Cw

"Dave Peterson" wrote:

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

Changing the format of the cell shouldn't make a difference.

The values in those cells COULD make a difference.

Cheese_whiz wrote:

Hi Dave,

Thanks for the reply. I finally figured out what the deal was and I feel
kinda dumb for not rolling through and checking more closely. User, after
utilizing the file for a while, all the sudden changed the date format she
used in two different/random cells in the column that was part of the pair
that didn't work.

I guess I need to investigate Excel or vba validation solution for these
cells.

Thanks again, though.
Cw

"Dave Peterson" wrote:

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


--

Dave Peterson