Network opt. with Solver: IF (range) vs SUMIF(range)

[inta9]

Hello everyone,

If I have labeled a vector, what's the difference in usage in IF (name) and SUMIF(name)?
My problem is, it works with SUMIF but not with IF, so I guess there must be some difference in the way both functions treat the data, the range. If I write SUMIF(name;H7;1) it seems to check whether any of the cells in "name" is equal to H7, but if I write IF(name=H7) it only checks for the element of "name" in the 7th cell.

greetings

[6StringJazzer]

Welcome to the Forum!

SUMIF specifically sums number through an entire range, and includes only those numbers that meet some condition. It is expecting a multicell range as part of the condition description.

But in IF, it just looks at whether a single logical expression returns TRUE or FALSE. In the expression

name=H7

Excel does not repeat the operation throughout all the cells specified by name. Excel can only check a single value to be equal to another single value. When you include a range with more than one cell in a test for equality, Excel returns a #VALUE! error, and that error is propagated back to the IF.

Offhand I can't think of a simple way to test an entire range for equality to some single cell, although there may very well be one. You can set up another vector that repeats the value you want to check against in every cell, and then use a formula like this:

=SUMPRODUCT(--(name=$B$1:$B$5))>0

It will return TRUE if there are any matches, and FALSE if none.