SUM.IF

%3CLINGO-SUB%20id%3D%22lingo-sub-2091029%22%20slang%3D%22en-US%22%3ESUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091029%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20need%20help%20to%20a%20SUM.IF%20formula%2C%20would%20like%20to%20use%20a%20cell%20value%20as%20criteria.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%23%20Area%3A%20work%20in%20my%20formula.%3CBR%20%2F%3E%23%20Criteria%3A%20normally%20a%20text%20string%20is%20used%20e.g.%20%22John%22%20but%20I%20would%20like%20to%20use%20a%20text%20criterion%20which%20is%20taken%20from%20e.g.%20cell%20D45.%20%22John%22%20is%20in%20the%20criteria%20area%2C%20but%20I%20have%20to%20write%20more%20than%20100%20different%20criteria%20(Peter%2C%20Anna%2C%20........)%20and%20it%20will%20be%20much%20easier%20to%20only%20have%20to%20write%20one%20criterion%20once%20in%20the%20lookup%20area%3CBR%20%2F%3E%23%20Sum_Range%3A%20are%20numeric%20and%20work.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ECan%20I%20write%20%22SUM.IF%20(a1%3A%20a100%3B%20f20%3B%20c1%3A%20c100)%20where%20c20%20in%20the%20example%20is%22%20John%20%22what%20does%20the%20formula%20look%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2091029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091048%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940466%22%20target%3D%22_blank%22%3E%40Refour1830%3C%2FA%3E%26nbsp%3BPerhaps%20best%20to%20demonstrate%20the%20working%20of%20SUMIF%20in%20a%20working%20example.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091135%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940466%22%20target%3D%22_blank%22%3E%40Refour1830%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBuilding%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B's%20example%2C%20the%20criteria%20can%20be%20presented%20as%20an%20array%20to%20yield%20an%20array%20of%20results.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20838px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248994i61C41772226BDABC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3ECSE%20will%20be%20needed%20for%20Excel%20versions%20other%20than%20365%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096637%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20very%20much%20was%20blinded%20by%20text%20format%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM.HVIS(%24F%245%3A%24F%2432%3B%22%3DCaCO2%22%3BL%245%3AL%2432)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20works%20now%20%3CSPAN%20class%3D%22lia-unicode-emoji%22%20title%3D%22%3Athumbs_up%3A%22%3E%3CLI-EMOJI%20id%3D%22lia_thumbs-up-tone-1%22%20title%3D%22%3Athumbs_up%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-unicode-emoji%22%20title%3D%22%3Athumbs_up%3A%22%3E%3CLI-EMOJI%20id%3D%22lia_thumbs-up-tone-1%22%20title%3D%22%3Athumbs_up%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there, need help to a SUM.IF formula, would like to use a cell value as criteria.


# Area: work in my formula.
# Criteria: normally a text string is used e.g. "John" but I would like to use a text criterion which is taken from e.g. cell D45. "John" is in the criteria area, but I have to write more than 100 different criteria (Peter, Anna, ........) and it will be much easier to only have to write one criterion once in the lookup area
# Sum_Range: are numeric and work.


Can I write "SUM.IF (a1: a100; f20; c1: c100) where c20 in the example is" John "what does the formula look like?

3 Replies

@Refour1830 Perhaps best to demonstrate the working of SUMIF in a working example. See attached.

@Refour1830 

Building on @Riny_van_Eekelen 's example, the criteria can be presented as an array to yield an array of results.

image.png

CSE will be needed for Excel versions other than 365

@Riny_van_Eekelen Thanks very much was blinded by text format 

 

=SUM.HVIS($F$5:$F$32;"=CaCO2";L$5:L$32)

 

If works now