SumPRODUCT - calculating result??

  • Thread starter Thread starter Dhazmo
  • Start date Start date
D

Dhazmo

Parameters:
Column A = Text
Column B = Text
Column C = Numerical

My formula is:

=SUMPRODUCT((Data!$A$2:$A$32001=Sheet2!$A3)*(Data!$B$2:$B$32001=H$1),Data!$C$2:$C$32001)

This works fine except where the value/text I want returned in column C
looks like this "2008: 36" or "2008-36" - instead I receive "0" or "84" -
when I want the formula to return exactly what is in the cell.

Please help!!!
 
You have to convert the underlying text. Sumproduct will do the anayltics
for you, but it won't do any kind of text conversion for you. Or maybe in
certain circumstances it will, btu the data still has to be consistent.

HTH,
Ryan---
 
If you are looking to retrive the matching data from ColC for the 2 criterias
metioned try the below. Please note that this is an array formula. You create
array formulas in the same way that you create other formulas, except you
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

=INDEX(Data!$C$2:$C$32001,MATCH(1,(Data!$A$2:$A$32001=Sheet2!$A3)*
(Data!$B$2:$B$32001=H$1),0))

If this post helps click Yes
 
"2008 :36", if you type it directly into a cell (without the quote marks)
Excel thinks you are trying to give a time value (because of the colon). When
that time value is put in a cell formatted as a number, you will get 83 and
change (rounds up to 84)

If you want to get it in your new cell in exactly the same format, precede
the value with an apostrophe, maybe something like (untested):

="'" &
SUMPRODUCT((Data!$A$2:$A$32001=Sheet2!$A3)*(Data!$B$2:$B$32001=H$1),Data!$C$2:$C$32001)
 
This worked a treat! many thanks!!

Jacob Skaria said:
If you are looking to retrive the matching data from ColC for the 2 criterias
metioned try the below. Please note that this is an array formula. You create
array formulas in the same way that you create other formulas, except you
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

=INDEX(Data!$C$2:$C$32001,MATCH(1,(Data!$A$2:$A$32001=Sheet2!$A3)*
(Data!$B$2:$B$32001=H$1),0))

If this post helps click Yes
Click to expand...
 
Back
Top