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?

"JLatham" wrote:

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?



"Secret Squirrel" wrote:

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)))