Jan 24 2021 01:05 AM
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?
Jan 24 2021 01:39 AM
@Refour1830 Perhaps best to demonstrate the working of SUMIF in a working example. See attached.
Jan 24 2021 02:37 AM
Building on @Riny_van_Eekelen 's example, the criteria can be presented as an array to yield an array of results.
CSE will be needed for Excel versions other than 365
Jan 25 2021 08:52 PM
@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 👍🏻👍🏻