Real problem with SumProduct

[Alexey]

Hi

I am analyising a lot of data. I am using:
=SUMPRODUCT(--(TEXT(MISDATA!$G$3:$G$5000,"mmm-yy")=TEXT($A39,"mmm-yy")),--(MISDATA!$H$3:$H$5000="Paul
Mumford"),MISDATA!$I$3:$I$5000) to pull a lot of the data together

I have five similar to the above - the difference is just the name of the
person in the H3 column. My problem that I would like a 7th column which
will incloude all others names in H3 other than the six above. Can you say
.... <> "Name1" and <> "name2" and <> "Name 3" etc..?

Else has anyone any other idea how I can do this?

Thanks

A

 

[Bernard Liengme]

Why not something like
=SUMPRODUCT(--(TEXT(MISDATA!$G$3:$G$5000,"mmm-yy")=TEXT($A39,"mmm-yy")),MISDATA!$I$3:$I$5000) - SUM(the other six values to the left)best wishes--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"Alexey" <[email protected]> wrote in messageHi>> I am analyising a lot of data. I am using:>=SUMPRODUCT(--(TEXT(MISDATA!$G$3:$G$5000,"mmm-yy")=TEXT($A39,"mmm-yy")),--(MISDATA!$H$3:$H$5000="Paul Mumford"),MISDATA!$I$3:$I$5000) to pull a lot ofthe data together>> I have five similar to the above - the difference is just the name of theperson in the H3 column. My problem that I would like a 7th column whichwill incloude all others names in H3 other than the six above. Can you say... <> "Name1" and <> "name2" and <> "Name 3" etc..?>> Else has anyone any other idea how I can do this?>> Thanks>> A>>

 

[Alexey]

Bernard

very many thanks - that's great - dont kn ow why I didn't think of it

A