Referencing a cell within SUMPRODUCT

[askweklar]

I am using the following formula: =(SUMPRODUCT(--(TRIM(All!$L$2:$L$10000)="Name"),--(TRIM(All!$M$2:$M$10000)="4"),--(TRIM(All!$J$2:$J$10000)="X")))/(SUMPRODUCT(--(TRIM(All!$L$2:$L$10000)="Name"),--(TRIM(All!$M$2:$M$10000)="4"))) It works, BUT I need to change the name "Name" and number "4" everyline - both of these can be found in a cell on the page - can this formula reference a cell so that I don't have to manually change 140 formulas? Clarify: the formula is pulling information from another page - on the page where the formula is found there is a column with individuals names in it, the formula is pulling from data to create percentages for those individuals. Rather than change the individual name for each individual in the list I'm *hoping* that the formula can just reference the cell with the name in it.

Thanks!

 

[Scott Huish]

Sure, the same as in any other formula:

=(SUMPRODUCT(--(TRIM(All!$L$2:$L$10000)=$A$1),--(TRIM(All!$M$2:$M$10000)=$B$1),--(TRIM(All!$J$2:$J$10000)="X")))/(SUMPRODUCT(--(TRIM(All!$L$2:$L$10000)=$A$1),--(TRIM(All!$M$2:$M$10000)=$B$1)))

 

[askweklar]

Thank you, I tried that - but it's not working... after I enter the cell the formula gives me an error saying that it's dividing by zero or blank cells.

 

[barry houdini]

If the 2nd SUMPRODUCT returns a result of zero then you are dividing by zero....so #DIV/0! is a legitimate result in that case - or do you expect a non-zero result for that part of the formula?

Did you use the exact formula HP suggested?

Which version of Excel are you using?

 

[askweklar]

Yes, I tried the exact formula. I do not expect a return of zero. When I have the formula filled with the actual name and number rather referencing the cell with the name and number I get a return of 14%. I have 100 individuals to generate stats for and will have to change name and number twice within each formula... if I could avoid manually changing each formula that would be awesome!

 

[askweklar]

Sorry, Barry, I'm using windows 7

 

[barry houdini]

In your original formula you are using a number in quotes as the criteria ("4"). Those quotes mean that the number is text rather than numeric - if you are using a cell reference in its place then you should ensure that the cell used is formatted as text.....or enter like this

'4

Does that work?

 

[askweklar]

YES!! Thank you, thank you so much!!