Adding an OR to SUM(IF....

[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?

[Biff]

Hi!

Try this (not an array, normally entered):

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

Biff

"JustMe602" <[email protected]> wrote
in message news:[email protected]...
>
> =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?
>
>
> --
> JustMe602
> ------------------------------------------------------------------------
> JustMe602's Profile:
> http://www.excelforum.com/member.php...o&userid=27854
> View this thread: http://www.excelforum.com/showthread...hreadid=498002
>

[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.

[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

"JustMe602" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> JustMe602
> ------------------------------------------------------------------------
> JustMe602's Profile:
> http://www.excelforum.com/member.php...o&userid=27854
> View this thread: http://www.excelforum.com/showthread...hreadid=498002
>