SumPRODUCT - calculating result??

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

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

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
 
K

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

Dhazmo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top