Sumproduct and/or summing array with multiple conditions

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!D1:D7))))}

I have also tried:

=sumproduct(and(Worksheet2!C1:C7=A$1,Worksheet2!
A1:A7=A,Worksheet2!B1:B7="D")*Worksheet2!D1:D7)
 

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