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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies