results of "SUMIF" test criteria

[windsurferLA]

I’m trying to understand how the various sumif and sumproduct functions
work, because I want to sum up data based format of the cell containing
the data.

Please note that I'm using EXCEL97 with all of the latest Microsoft
patches.

If seen the following formula used to sum the values in column “B” where
column “A” contains the term “income”.

=SUMPRODUCT((A2:A6="Income")*(B2:E6))

The fact that it works implies that the test
(A2:A6="Income")
returns a value of unity when “true” rather than the text “true”.
The SUMPRODUCT FUNCTION then performs the equivalent of a matrix
multiplication and returns the sum.
I find it rather strange that the test returns the value unity. HOW COME?

I’ve also observed that if I place the function formula.
=Cell(“format”,B2:E6)
any place else on the worksheet,
the SUMPRODUCT formula shown above returns “#value”.

WHY SHOULD A FORMULA IN ONE PART OF WORKSHEET IMPACT RESULTS IN ANOTHER?

I’ve also observed that if I use the formula
=Cell(“format”,B2:E6)
and then change the formatting of a cell within the range being tested,
such as B2,
the output of =Cell(“format”,B2:E6) does not change until I force a
recalculation even though the sheet is set for automatic recalc. WHY?

 

[Guest]

For a simple, single-condition test, I much prefer sumif:
=sumif(a:a,"Income",b:b). But sumproduct does offer a great deal of
flexibility for more complicated conditions. For some great information on
how it works, and why, check
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Bernard Liengme]

When you do an arithmetic operation on Boolean values (or on text values
that represents digits), Excel converts the Boolean (text) to a number.
Try this:
A1 = apple
B1 =apple
B2 =B1=A1
B3 = B2*1
B4 =--(B2)
B5 =B2+7

D1 =happy2005
D2 =LEFT(D1,5)&RIGHT(D1,4)+1)

best wishes

 

[Guest]

On the first point you are correct, this syntax for a conditional sum
function does assume that "True" = 1 to give the correct answer to your
formula.

However, this syntax should only work reliably if it is entered as an array
function
- you need to type [Ctrl][Shift] and [Enter] together to enter the formula,
this will result in the curly brackets {} around the function that others
have noted.

Utilising this slightly modified syntax will avoid the #value on recalc.

Kind Regards

 

[windsurferLA]

In your message you showed the expression B4 =--(B2)

I've seen the double dash or double negative signs elsewhere. Does it
have a meaning other than that of applying the negation twice?

WindsurferLA

 

[windsurferLA]

Moving on to my real problem:
I want to sum only those cells in a row that are displayed using the
“General†format. I could write a macro to step through all of the
cells, check their formatting, and perform a summation, but
operationally it is simpler to give user a formula that can copied down
the spread sheet.

The function CELL(“formatâ€, MN) returns the character “G†if the
referenced cell is formatted using the general format. I’ve tried two
variations of placing this test within an IF statement, and neither one
works independent of whether the formula is entered as a regular formula
or as an array formula.


=SUM(IF(CELL("format",C28:L28)="G",C28:L28,0))

=SUMPRODUCT(IF(CELL("format",C34:L34)="G",1,0)*C34:L34)

Can the Cell(“formatâ€, MN) test be used within an “if†statement in XL97?


WindsurferLA

 

[windsurferLA]

Update... I now see that unary operator, --, is to prevent #ERROR valves
from stopping the process.

WindsurferLA

 

[Bernard Liengme]

I think you will need a 'helper' row because the CELL function used to
detect format cannot work with an array.
In C29 enter: =CELL("format",C28) and copy to L29
To get your sum use: =SUMIF(C29:L29,"G",C28:L28)
best wishes

 

[Dave Peterson]

It can convert text numbers to number numbers, too.

But in this case, it's used to convert True/False to 1/0.

 

[windsurferLA]

I think you will need a 'helper' row because the CELL function used to
detect format cannot work with an array.

I suspected same, but I had hoped it was not true. In hind sight, I
think I can see why. I note that the Cell function CELL("format') seems
to require free cells to the right of cell being examined, possibly to
store some intermediate data. When one is trying to examine an array,
those cells are not free and open.

Thanks..

WindsurferLA

 

[Bernard Liengme]

Here is a UDF that works for me:

Function SumGeneral(rng As Range)
'Debug.Print "start"
Dim cell As Range
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.NumberFormat = "General" Then
SumGeneral = SumGeneral + cell.Value
End If
End If
' Debug.Print "G"; cell; SumGeneral
Next cell
'Debug.Print "End"
End Function

 

[windsurferLA]

Subsequent to my last post, I hit upon a much simpler solution to my
problem of separating quantities from dates. I merely test on the
magnitude of the number in the cell, and if it is greater than 20,000,
it is ignored. A magnitude test can be implemented easily with either
SUMIF or SUMPRODUCT entered as an array function. The resulting formula
is simple enough that users can copy it from row to row, and sheet to sheet.

The solution works because the numeric values of the date codes are much
larger than the quantities. For dates after 1960, the smallest date
code is about 30,000. Meanwhile, the quantities being recorded rarely
reach more than a few hundred.

Thanks for the UDF code. Your idea to fold macro-like code into a UDF is
something I had not considered, but opens up lots of possibilities to
expedite other operations. I'm copying it down for possible use elsewhere.

Windsurfer LA