sumproduct issue

[jw01]

hello

having some difficulty or wondering what the issue is with my sumproduct formula

essentially, i have 3 criterias
1) code
2) month
3) sum net sales

=SUMPRODUCT(('AY_WEB0000_000 10 '!$B$25:$B$16636=(VALUE(Sheet1!$B11))*('AY_WEB0000_000 10 '!$C$25:$C$16636=Sheet1!$B$8)*('AY_WEB0000_000 10 '!$I$25:$I$16636)))

i am getting zero as the result....i have highlighted it in red in my attached sheet.

pls let me know. thank you.

[Norie]

Replace VALUE(Sheet1$B11) with TEXT(Sheet1$B11, "@").

Or wrap 'AY_WEB0000_000 10 '!$B$25:$B$16636 with VALUE.

[jw01]

hey thanks so much.

quick question....once i include "TEXT(Sheet1$B11, "@")"....will that impact months like 10, 11, 12 or like consider them as month 1?

[jw01]

hey

for some odd reason, the file is showing a variance of "2014" and "2013" when i sum the total - which are the respective years.

i am not sure why it is summing up the year into the equation?



pls see attached - formula in cell D11:
=SUMPRODUCT(('AY_WEB0000_000 10 '!$B$25:$B$16636=(TEXT(Sheet1!$B11,"@")))*('AY_WEB0000_000 10 '!$C$25:$C$16636=Sheet1!$B$8)*('AY_WEB0000_000 10 '!$I$25:$I$16636))

[Jacc]

Try:

=SUMPRODUCT(--(VALUE('AY_WEB0000_000 10 '!$B$25:$B$16636)=Sheet1!$B11),--('AY_WEB0000_000 10 '!$C$25:$C$16636=Sheet1!$B$8),--('AY_WEB0000_000 10 '!$I$25:$I$16636))

[jw01]

guys...thx u so much...

i was adding the year in the column...opps