SUM.IF

Copper 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 👍🏻👍🏻