Sumproduct

**stait** · 09-25-2011, 08:29 PM

I am trying to return a value in a cell if criteria is met in other cells. For example, if A1:A18=Yes, B1:B18=Blue, D1:D18=Car, enter the value in T1:T18

**teylyn** · 09-25-2011, 08:32 PM

Hello,

Sumproduct will return a sum of values or a count of values.

You cannot use Sumproduct to return a value in a range. That requires a lookup formula instead.

What exactly do you want to achieve? Please post a data sample in a workbook.

cheers,

**stait** · 09-25-2011, 08:41 PM

Originally Posted by teylyn

Hello,

Sumproduct will return a sum of values or a count of values.

You cannot use Sumproduct to return a value in a range. That requires a lookup formula instead.

What exactly do you want to achieve? Please post a data sample in a workbook.

cheers,

I have a sheet which lists a number of various products by similar products. If "Yes" in column A is select, and column B = Blue and column D = Car, I want the value found in column T to be present

**teylyn** · 09-25-2011, 08:45 PM

In T1

=if(and(A1="Yes",B1="Blue",D1="Car"),T1,"")

copy down

If that does not help, post a workbook.

**stait** · 09-25-2011, 08:53 PM

I have attached the file. What I want is IF I select Yes in Column A, and it is a Diswasher (column B), a Jenn-Air (column C), then the Total Value in Column K will be posted in C24

Thank you again

**teylyn** · 09-25-2011, 08:59 PM

try

=SUMPRODUCT(--($A$1:$A$18="yes"),--($B$1:$B$18="Dishwasher"),--($C$1:$C$18="Jenn-Air"),$K$1:$K$18)

This would work much better with a pivot table.