Home

SUMPRODUCT?

%3CLINGO-SUB%20id%3D%22lingo-sub-1014052%22%20slang%3D%22en-US%22%3ESUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014052%22%20slang%3D%22en-US%22%3E%3CP%3Eseeking%20an%20array%20formula%20for%20the%20following%3A%3C%2FP%3E%3CP%3EIf%20the%20file%20Defense%2C%20worksheet%20Chart1%2C%20Column%20A%20equals%20the%20file%20PlayerReport%2C%20worksheet%20Opponents%2C%20Column%20A%2C%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3Eif%20the%20file%20Defense%2C%20worksheet%20Chart1%2C%20Column%20C%20equals%20the%20file%20PlayerReport%2C%20worksheet%20Opponents%2C%20Column%20Z%26nbsp%3B%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3Eif%20the%20file%20Defense%2C%20worksheet%20Chart1%2C%20Columns%20L%20and%20M%20are%20greater%20than%20zero%3C%2FP%3E%3CP%3Ethen%3C%2FP%3E%3CP%3Esum%20all%20matching%20rows%20in%26nbsp%3Bthe%20file%20Defense%2C%20worksheet%20Chart1%2C%20Column%20F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1014052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014061%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014061%22%20slang%3D%22en-US%22%3EPlease%20clarify%20your%20requirements%20by%20attaching%20your%20sample%20Excel%20file%20with%20your%20manually%20entered%20results.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014095%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014095%22%20slang%3D%22en-US%22%3E%3CP%3EFile%20is%20attached...thanks.%20%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014182%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20also%20attach%20the%20PlayerReport%20file%20with%20the%20Opponents%20worksheet%20in%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014210%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014210%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025333%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025333%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025354%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20unknown%20reasons%2C%20I%20cannot%20view%20your%20PlayerReport%20file.%20Please%20reduce%20both%20files%20to%20their%20barest%20minimum.%20Thereafter%2C%20attach%20them%20again.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1027476%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1027476%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%20Try%20this%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014093%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014093%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029446%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1029446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%20I%20cannot%20still%20view%20its%20contents%2C%20probably%20because%20it%20contains%20links%20I%20have%20no%20access%20to.%20Perhaps%2C%20you%20first%20convert%20the%20contents%20to%20values%20before%20you%20attach%20it%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1032378%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1032378%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20patience.%20I%20have%20attached%20both%20files%20in%20question%20and%20have%20changed%20all%20formulas%20to%20values.%20Thanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
rbellotti
Occasional Contributor

seeking an array formula for the following:

If the file Defense, worksheet Chart1, Column A equals the file PlayerReport, worksheet Opponents, Column A,

and

if the file Defense, worksheet Chart1, Column C equals the file PlayerReport, worksheet Opponents, Column Z 

and

if the file Defense, worksheet Chart1, Columns L and M are greater than zero

then

sum all matching rows in the file Defense, worksheet Chart1, Column F 

 

Thank you!

 

10 Replies
Highlighted
Please clarify your requirements by attaching your sample Excel file with your manually entered results.
Highlighted
Highlighted

File is attached...thanks. @Twifoo 

Highlighted

@rbellotti 

Please also attach the PlayerReport file with the Opponents worksheet in it.

Highlighted
Highlighted

No solution?

Highlighted

@rbellotti 

For some unknown reasons, I cannot view your PlayerReport file. Please reduce both files to their barest minimum. Thereafter, attach them again. 

Highlighted

Thank you. Try this one.

 

@Twifoo 

Highlighted

@rbellotti 

I'm sorry I cannot still view its contents, probably because it contains links I have no access to. Perhaps, you first convert the contents to values before you attach it here.

Highlighted

@Twifoo 

 

Thank you for your patience. I have attached both files in question and have changed all formulas to values. Thanks again.