SUMPRODUCT problem

[raehippychick]

Hi all

Me again with another SUMPRODUCT confusion

I am trying to show many cells have the word SESSION in them, but they have to also match up to the code 73 in column E, what is in Cell L3 must find a match in column C

So I can see that PROK has 3 instances of it relating to 73

So in Cell Q3 I put this…


=SUMPRODUCT(IF(--(C3:C33829=L3)*(E3:E33829=73))*(G3:G33829="*SESSION*))

… and it didn’t work! I have tried a few options but mostly Excel seems to object to my number 73 in the formula regardless of whether or not I put quotes round it

Any help greatly appreciated

[daddylonglegs]

You don't normally need IF within SUMPRODUCT, you don't need the -- here and you can't use wildcards with a direct comparison, as your example cells contain only the single word try

=SUMPRODUCT((C3:C33829=L3)*(E3:E33829=73)*( G3:G33829="SESSION"))

[corinereyes]

Try this:

Please Login or Register  to view this content.

[raehippychick]

Thank you for your replies

Unfortunately I can't get either one to work - the problem I have is that the cells with SESSION in will, in real life have other words too. These cells should have only the words SESSION FILE in them, but as they are being input by hand in a free text field of a database sometimes people use this field to include other info, hence my need to search only on the word session within the cell!

(hopefully going forward the databse will get redesigned so this will be a dropdown selection or similar, but in the meantime...)

Sorry, my fault for not explaining that in my first post

[corinereyes]

Try:

Please Login or Register  to view this content.

[NBVC]

Thank you for your replies

Unfortunately I can't get either one to work - the problem I have is that the cells with SESSION in will, in real life have other words too. These cells should have only the words SESSION FILE in them, but as they are being input by hand in a free text field of a database sometimes people use this field to include other info, hence my need to search only on the word session within the cell!

(hopefully going forward the databse will get redesigned so this will be a dropdown selection or similar, but in the meantime...)

Sorry, my fault for not explaining that in my first post

Try:

Please Login or Register  to view this content.

[raehippychick]

Fantastic - that works

thank you so much

R