Adding an OR to SUM(IF....

J

JustMe602

=SUM(IF(MONTH($A7)=MONTH(DTd)*IF(DTam="M",1,0),DTeb),IF(MONTH($A7)=MONTH(DTd)*IF(DTam="A",1,0),DTeb))


I would like to combine if possible to read something like this?
Doable?

=SUM(IF(MONTH($A7)=MONTH(DTd)*IF(OR(IF(DTam="M",1,0),IF(DTam="A",1,0)),DTeb)))


I can't seem to get the OR to work? Basically I am trying to simplify
the formula.

Let me know if I need to clarrify this any further?
 
B

Biff

Hi!

Try this (not an array, normally entered):

=SUMPRODUCT(--(MONTH(DTd)=MONTH($A7)),--(ISNUMBER(MATCH(DTam,{"M","A"},0))),DTeb)

Biff
 
J

JustMe602

Okay thanks for the reply it but I am confused on the syntax. And how
and why it worked.

=SUMPRODUCT(--(MONTH($A7)=MONTH(DTd2006)),--(ISNUMBER(MATCH(DTam2006,{"M","A"},0))),DTeb2006)


What does the "--" two dashes do?

I guess I really don't understand how the SUMPRODUCT works.

Before the first comma, for instance, this matches where the two
columns/ranges equal the same month. Then the ISNUMBER(MATCH... this
section looks for intances where DTam2006 equals "M" or "A" then brings
back the information in the range DTeb2006. But why without an if
statement?

Please if anyone could help explain this it would be greatly
appriciated and again thanks for the formula.

JustMe.
 
B

Biff

Hi!

The formula is operating on 3 arrays:

DTd2006
DTam2006
DTeb2006

On the first 2 arrays you're performing a logical test:

MONTH($A7)=MONTH(DTd2006)
ISNUMBER(MATCH(DTam,{"M","A"},0)

The results of those tests will return the logical values of either TRUE or
FALSE.

The "--" converts those to numeric values where TRUE = 1 and FALSE = 0.

Then all 3 arrays are multiplied together and then the results of the
multiplication are summed together to get the final result. It would look
like this:

1*1*10 = 10
1*0*12 = 0
0*0*15 = 0
0*1*10 = 0
1*1*20 = 20

result = 30

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Biff
 
Top