SumPRODUCT - calculating result??

[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!!!

 

[ryguy7272]

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---

 

[Jacob Skaria]

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

 

[David Biddulph]

SUMPRODUCT works with numbers; you can't take a PRODUCT or SUM of text.

 

[ker_01]

"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)

 

[Dhazmo]

This worked a treat! many thanks!!

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