Trouble with Sum(if...)

[plant007]

HI All

I posted on this site a few days ago and was helped greatly by people on here. I have tried to build on that work but I have hit a wall. I am trying to write a formula;

'If cell/s in column A is say dept X , then if the correponding entries in column D are numbers, if the corresponding entries in column D are not equal to the corresponding entries in column C, then count where the corrrespondng entries in column F are less than or equal to the corresponding entries in Column D

To help
Column A = Dept
Column C = Planned Date
Column D = Latest Approve Date
Column F = Completion date

My equation so far;

{=SUM(IF($A$50:$A$62="MoD",ISNUMBER($D$50:$D$62),$D$50:$D$62<>$C$50:$C$62),$F$50:$F$62<=$D$50:$D$62)}

I get the result 0 when it should be a 3

Any help greatly appreciated
Thanks
Andy

 

[VoG]

Possibly

=SUMPRODUCT(--(A1:A100="Dept X"),--ISNUMBER(D1:D100),--(D1:D100<>C1:C100),--(F1:D100<c1:c100),--(f1:f100><=D1:D100))</c1:c100),--(f1:f100>

 

[taurean]

You beat me to it:

=SUMPRODUCT(--(A2:A4="Dept X"),--(C2:C4<>D2:D4),--(F2:F4<=D2:D4))

Plus I missed second part but I guess it has to be date always.

Just to add a bit of value, take a look at this:
http://www.vbaexpress.com/forum/forumdisplay.php?f=98
Very good explanation SUMPRODUCT formula by Bob Philips aka XLD.

 

[Aladin Akyurek]

HI All

I posted on this site a few days ago and was helped greatly by people on here. I have tried to build on that work but I have hit a wall. I am trying to write a formula;

'If cell/s in column A is say dept X , then if the correponding entries in column D are numbers, if the corresponding entries in column D are not equal to the corresponding entries in column C, then count where the corrrespondng entries in column F are less than or equal to the corresponding entries in Column D

To help
Column A = Dept
Column C = Planned Date
Column D = Latest Approve Date
Column F = Completion date

My equation so far;

{=SUM(IF($A$50:$A$62="MoD",ISNUMBER($D$50:$D$62),$D$50:$D$62<>$C$50:$C$62),
$F$50:$F$62<=$D$50:$D$62)}

I get the result 0 when it should be a 3

Any help greatly appreciated
Thanks
Andy

Continuing with the set up you attempted...

Control+shift+enter, not just enter:

=SUM(
     IF($A$50:$A$62="MoD",
     IF(ISNUMBER($D$50:$D$62),
     IF(1-($D$50:$D$62=$C$50:$C$62),
     IF($F$50:$F$62<=$D$50:$D$62,1)))))

 

[plant007]

Many thanks VoG, taurean and Aladin for your help. Weekend now so I will plug these formulas in next week

Have a nice w/e
Andy

 

[plant007]

Many thanks for all your help - each formula works beautifully!

 

[Aladin Akyurek]

Many thanks for all your help - each formula works beautifully!

You are welcome. Thanks for providing us feedback.