=SUM(IF(MONTH($A7)=MONTH(DTd)*IF(DTam="M",1,0),DTe b),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

Hi!

Try this (not an array, normally entered):

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

Biff

"JustMe602" wrote
in message ...

=SUM(IF(MONTH($A7)=MONTH(DTd)*IF(DTam="M",1,0),DTe b),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

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

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" wrote
in message ...

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