SOLVED
Home

Sumproduct formular not working as expected

%3CLINGO-SUB%20id%3D%22lingo-sub-694362%22%20slang%3D%22en-US%22%3ESumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694362%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20modified%20a%20sumproduct%20formula%20and%20it%20is%20not%20working%20as%20expected.%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((Sales!B%243%3AB%24999%3DA6)*(Sales!F%243%3AF%24999))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20previously%26nbsp%3B%3DSUMPRODUCT((Sales!B%243%3AB%24600%3DA6)*(Sales!F%243%3AF%24600))%3C%2FP%3E%3CP%3EIt%20is%20not%20picking%20up%20any%20value%20after%20row%20600%20in%20the%20range.%3C%2FP%3E%3CP%3EAny%20help%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-694362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694376%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694376%22%20slang%3D%22en-US%22%3EPlease%20specify%20the%20result%20of%20your%20modified%20formula%20and%20the%20result%20you%20expected%20from%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694406%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694406%22%20slang%3D%22en-US%22%3ECan%20I%20send%20the%20excel%20file%3F%20It%20would%20be%20easier%20to%20understand.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694408%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694408%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formulae%20are%20on%20the%20Gasoline%20recon%20sheet%20range%20E6%20to%20E36.%20I%3C%2FP%3E%3CP%3EThe%20should%20be%20picking%20up%20results%20from%20column%20F%20of%20the%20Sales%20sheet.%3C%2FP%3E%3CP%3ESince%20I%20modified%20the%20formula%20to%20include%20rows%20601%20to%20999.%20It%20is%20not%20picking%20up%20those%20values%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694420%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360873%22%20target%3D%22_blank%22%3E%40derick1560%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20blank%20cells%20in%20column%20B%20of%20Sales%20for%20these%20rows%2C%20thus%20formula%20returns%20nothing.%20I'm%20not%20sure%20why%20do%20you%20have%20dates%20both%20in%20columns%20A%20and%20B%2C%20but%20you%20shift%20on%20column%20A%20in%20formula%20it%20returns%20correct%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711140%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20Am%20almost%20embarrassed.%20I%20created%20the%20workbook%20more%20than%2010%20years%20ago%20and%20forgot%20the%20formula%20was%20looking%20for%20the%20values%20in%20column%20B%20and%20not%20column%20A.%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711180%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20formular%20not%20working%20as%20expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360873%22%20target%3D%22_blank%22%3E%40derick1560%3C%2FA%3E%26nbsp%3BI%20see%2C%20perhaps%20it's%20time%20to%20update%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
derick1560
Occasional Contributor

I have modified a sumproduct formula and it is not working as expected.

=SUMPRODUCT((Sales!B$3:B$999=A6)*(Sales!F$3:F$999))

 

It was previously =SUMPRODUCT((Sales!B$3:B$600=A6)*(Sales!F$3:F$600))

It is not picking up any value after row 600 in the range.

Any help appreciated.

 

Thanks

 

 

 

6 Replies
Please specify the result of your modified formula and the result you expected from it.
Can I send the excel file? It would be easier to understand.

@Twifoo 

The formulae are on the Gasoline recon sheet range E6 to E36. I

The should be picking up results from column F of the Sales sheet.

Since I modified the formula to include rows 601 to 999. It is not picking up those values

Thank you.

Solution

@derick1560 

 

You have blank cells in column B of Sales for these rows, thus formula returns nothing. I'm not sure why do you have dates both in columns A and B, but you shift on column A in formula it returns correct result.

@Sergei Baklan 

Thank you. Am almost embarrassed. I created the workbook more than 10 years ago and forgot the formula was looking for the values in column B and not column A.

Thanks again.

@derick1560 I see, perhaps it's time to update the workbook.