Count Cells depending upon Multiple cell Values -

[chippymark]

Ok well here goes, for those of you advanced I will try to do my best to get across what I am looking to achieve: Please Bear with me.
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set,
So I'd expect E2 in the case below to read 1.83, But I would not want double postings ????
Is this feasable or am I expecting too much.

thanks in advance

Mark

Order No OP Posted Set Allowed Set
30761157 0010 0.73 1.500
30761157 0010 0.50 0.000
30761157 0010 0.60 0.000
30764610 0010 0.00 0.000
30764610 0010 1.05 0.500
30765244 0010 0.00 0.000
30765244 0010 0.55 0.500
30766396 0010 0.00 0.000
30766396 0010 1.18 0.500
30768109 0010 0.00 0.000
30768629 0010 0.00 0.000
30768629 0010 0.00 0.000
30768629 0010 0.00 0.000

 

[chippymark]

THE LAST FORMULA POSTED A 0 WHERE IT SHOULD OF POSTED 1.83 ????

I HAVE TRIED THE FOLOWWING FORMULA, JUST TO GIVE YOU SOME IDEA OF WHAT i AM TRYING TO ACHIEVE.

=SUMPRODUCT((D2>0)*(A:A=A2)*(B:B=B2),(C:C))

CHEERS..

MARK

Soooo Close yet sooooo far

 

[ADAMC]

EDIT

Hi Mark,

neither formula posted returned a zero value...Both returned 1.83...I made an error but went back and re-edited.

 

[barry houdini]

Hello mark,

Did you try my suggestion a few posts back?

in E2 copied down

=IF(D2>0,SUMPRODUCT(--(A$2:A$14=A2),--(B$2:B$14=B2),C$2:C$14),"")

extend ranges beyond row 14 if necessary. You can't use whole column references with SUMPRODUCT

 

[chippymark]

Mate !!! what can I say, but Thank you.....
I think it now works using the =IF(D2>0,SUMPRODUCT(--(A$2:A$14=A2),--(B$2:B$14=B2),C$2:C$14),"")

So I have sat up long enough now on worl related problems, so I am going to hit the sack.. and will return to the job back at work tomorrow.

Cheers...

And thanks