more sum product
 

I am still working on a Plan vs Actual Date chart

I'm trying to compare the Plan Date and the Actual Date

=IF(SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<""))<0,SUMPRODUCT(--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<"")),"")

A12 is the name of the business unit
B2 is the date of tha month
AN is Plan Date
AO is ACtual Date


The problem is that I'm getting a double counts. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

more sum product
 

Shouldn't you be using

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:658ae240118a5@uwe...
I am still working on a Plan vs Actual Date chart

I'm trying to compare the Plan Date and the Actual Date

=IF(SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<""))<0,SUMPRODUCT(--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<"")),"")

A12 is the name of the business unit
B2 is the date of tha month
AN is Plan Date
AO is ACtual Date


The problem is that I'm getting a double counts. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

more sum product
 

Shouldn't you be using

=IF(SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odyssey!$N$2:$N$998=B2),--
(Odyssey!$O$2:$O$998<""))<0,SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odys
sey!$N$2:$N$998=B2),--
(Odyssey!$O$2:$O$998<"")),"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:658ae240118a5@uwe...
I am still working on a Plan vs Actual Date chart

I'm trying to compare the Plan Date and the Actual Date

=IF(SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<""))<0,SUMPRODUCT(--(Odyssey!$AN$2:$AN$998=B2),--
(Odyssey!$AO$2:$AO$998<"")),"")

A12 is the name of the business unit
B2 is the date of tha month
AN is Plan Date
AO is ACtual Date


The problem is that I'm getting a double counts. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

more sum product
 

not to be funny but what should I use?

Bob Phillips wrote:
Shouldn't you be using

=IF(SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odyssey!$N$2:$N$998=B2),--
(Odyssey!$O$2:$O$998<""))<0,SUMPRODUCT(--(Odyssey!$E$2:$E$998=A12),--(Odys
sey!$N$2:$N$998=B2),--
(Odyssey!$O$2:$O$998<"")),"")

I am still working on a Plan vs Actual Date chart

[quoted text clipped - 10 lines]

The problem is that I'm getting a double counts. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

more sum product
 

OK I got it, I need to add the condition (Odyssey!$E$2:$E$998=A12)

Thanks

You have been such a great help to me!



s2m wrote:
not to be funny but what should I use?

Shouldn't you be using

[quoted text clipped - 8 lines]

The problem is that I'm getting a double counts. Any suggestions?

--
Message posted via http://www.officekb.com