Formula Problem

[Guest]

I have the following formula in my worksheet but I'm getting a "#Value!"
error. Can anyone shed some light if there is a problem with my formula? When
my value in B3 is false it works fine but when it's true I get the error
message above.

=IF($B$3="",SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998)),SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998=Summary!$B$3)))

 

[Guest]

First, I make no pretence to be a worksheet function expert. However, this is
my read.

When B3 is blank the following is evaluated:
SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998))

When B3 contains a vendor name the following is evaluated:
SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998=Summary!$B$3))

Note that for the second snippet (when B3 contains name), for each element
in both arrays, all three arguments will evaluate to either 1 or 0 and the
sum of the product of these numerics will also be a numeric (1 or 0).
Therefore no error.

For the first snippet (when B3 is blank), the third argument
(Detail!$B$2:$B$43998) is only a list of names as I understand you. What is
the product of 1 * 1 * "Bob Smith" ??? Result is an error.

My read is that you can simply omit the third argument (list of names) and
it will return the desired count. Hope that helps.

Regards,
Greg

 

[Guest]

What is it supposed to be doing?
I see in the True condition for $B$3="" that it gives you the sum of the
values in Detail sheet for B2:B43998. If I can presume you're looking for
same from Summary sheet when $B$3<> "" then look at the comparison, I am
thinking that (Detail!$B$2:$B$43998=Summary!$B$3) is incorrect and should be
(Summary!$B2:$B43998)
instead. But this is all exactly reverse from what you say is and isn't
working: looks to me like true condition should work, false shouldn't.

In which case, perhaps the end of the first SUMPRODUCT() needs to look like
this:
(Detail!$B$2:$B$43998="")
If you're just trying to get counts, that would be my guess on it.

What's in Details!$B$3 and what's in Summary!$B$3?

 

[Guest]

Greg,
You've described it very well and come to the same conclusion that I did
earlier. And I believe that your offered solution is the way to fix it.

 

[Guest]

In cell B3 I have a name list that I'm using to validate my summary sheet.
When I select a vendor from my list it is matching the name with any rows on
my detail sheet. It looks for that vendor name in column B on my detail
sheet. Then it is summarizing the counts by month and year for that vendor.
What I'm trying to do is have it summarize all vendors when cell B3 is blank.
That way my users can get a total summary count and if they choose they can
select a vendor and just get that vendors monthly summary. Does that make
sense to you? It is working when I select a vendor in cell B3 but when I
clear that cell is when I get the error message. When it's false is when a
vendor is selected in cell B3 that's why I have the
(Detail!$B2:$B43998=Summary!$B$3) so it can match that vendor with my detail
sheet.

Any thoughts?

 

[Guest]

Thanks Greg! That worked perfectly!

First, I make no pretence to be a worksheet function expert. However, this is
my read.

When B3 is blank the following is evaluated:
SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998))

When B3 contains a vendor name the following is evaluated:
SUMPRODUCT(--(MONTH(Detail!$I$2:$I$43998)=MONTH($A7)),--(YEAR(Detail!$I$2:$I$43998)=YEAR($A7)),--(Detail!$B$2:$B$43998=Summary!$B$3))

Note that for the second snippet (when B3 contains name), for each element
in both arrays, all three arguments will evaluate to either 1 or 0 and the
sum of the product of these numerics will also be a numeric (1 or 0).
Therefore no error.

For the first snippet (when B3 is blank), the third argument
(Detail!$B$2:$B$43998) is only a list of names as I understand you. What is
the product of 1 * 1 * "Bob Smith" ??? Result is an error.

My read is that you can simply omit the third argument (list of names) and
it will return the desired count. Hope that helps.

Regards,
Greg

 

[Guest]

Thanks for the feedback. The OP's response to your post gave it away:

It looks for that vendor name in column B on my detail sheet.

Greg