S
Steven Cheng
I was wondering how I could setup the sumproduct or sum
formula properly.
I have a named range (A1:A2) that has been named "A".
Within this table, there are several items (A1,A2) as an
example.
In my data table, I have several a worksheet where
A B C D
A1 D 01/01/2003 100
A2 T 01/02/2003 200
A3 T 01/03/2003 300
A4 D 01/04/2003 400
A2 T 01/05/2003 500
A3 D 01/06/2003 600
A1 T 01/07/2003 700
In my third worksheet, I have dates along row 1 going from
(starting in cell A1) 01/01/2003 to 01/31/2003. Starting
in cell A54, I want to sum all values in Column D in the
data table where the dates in row 1 equals the
cooresponding value in column C, where B is equal to "D",
AND where the values in column A of the data table equals
one of the values in named range A.
I have tried the sumif formula:
{=sum(if(Worksheet2!C1:C7=A$1,if(Worksheet2!A1:A7=A,if
(Worksheet2!B1:B7="D",Worksheet2!D17))))}
I have also tried:
=sumproduct(and(Worksheet2!C1:C7=A$1,Worksheet2!
A1:A7=A,Worksheet2!B1:B7="D")*Worksheet2!D17)
formula properly.
I have a named range (A1:A2) that has been named "A".
Within this table, there are several items (A1,A2) as an
example.
In my data table, I have several a worksheet where
A B C D
A1 D 01/01/2003 100
A2 T 01/02/2003 200
A3 T 01/03/2003 300
A4 D 01/04/2003 400
A2 T 01/05/2003 500
A3 D 01/06/2003 600
A1 T 01/07/2003 700
In my third worksheet, I have dates along row 1 going from
(starting in cell A1) 01/01/2003 to 01/31/2003. Starting
in cell A54, I want to sum all values in Column D in the
data table where the dates in row 1 equals the
cooresponding value in column C, where B is equal to "D",
AND where the values in column A of the data table equals
one of the values in named range A.
I have tried the sumif formula:
{=sum(if(Worksheet2!C1:C7=A$1,if(Worksheet2!A1:A7=A,if
(Worksheet2!B1:B7="D",Worksheet2!D17))))}
I have also tried:
=sumproduct(and(Worksheet2!C1:C7=A$1,Worksheet2!
A1:A7=A,Worksheet2!B1:B7="D")*Worksheet2!D17)