Complicated Conditional Sum

[GaryE]

Hi all:

I posted this question last week but got no responses. I think tha
was because of the wording I choose. So let me try to state i
differently.

First I am using Excel 2000 (9.0.6926 SP3-3) -- (and no I can no
upgrade so don't even ask :-(

Here is a sample of my data in sheet 1 of the workbook:

Date Name Status Cost
1/1/06 GE C $1212
2/1/06 GE I $2323
1/5/06 XX C $3434
2/7/06 XX I $4545
1/8/06 GE I $5656
1/9/06 GE C $6767
. . .

Where Status C Indicates Completed
and Status I indicates Incomplete

Each column is a named array.

on another sheet (sheet 2)
A1 = Start Date
B1 = End Date
Row 2 = Name1, Name2, . . .

IN sheet 2 I need a formula in row 3 that will:
add the cost of all Completed sales made by Name between Start date an
end Date

I have tried:

{=sum(if((Date>=$A$1))*(Date<=$B$1)*(Status="c")*(Name=A2),Cost)}

but it doesn't work.

I have tried other variations to no avail. like:

{=sum(if(Date>=$A$1,if(Date<=$B$1,if((Status="c")*(Name=A2),Cost))))}

however I have gotten the following to work for a year to date total

{=sum(if((Name=A2)*(Status="c'"), Cost))}

I have tried everything I can think of and I am out of ideas.

Any and all help will be greatly appreciated!!!

Thanks,
Gar

 

[ChuckF]

Why don't you create a seperate column on sheet one (hiden so as to not
affect the look of the sheet with =IF(C6="I","",1) If the only
variable is I or C you can then add the sum of that new column.

 

[ChuckF]

oops, forgot you wanted to add the cell...obviously then instead of '1'
at the end of the formula you would want it to be C6.

 

[GaryE]

Chuck:

Thanks for the response. Unfortunately, there are 9 possibilites fo
the status column. But even if there were only 2 I'm not sure how you
suggestion would help. I still need to be able to perform th
conditional sum based on dates and names.

However you have given me an idea. Before I spew the idea out I wan
to try it. I will post the idea when I know if it works or not.

Thanks again,

Gary

 

[Ken Johnson]

Hi GaryE,

Using your sample data the following SUMPRODUCT formula resulted in
1212 for GE and 3434 for XX which look correct to me...

=SUMPRODUCT((Name=A2)*(Status="C")*(Date>=$A$1)*(Date<=$B$1)*(Cost))

Hope this helps.

Ken Johnson

 

[GaryE]

Chuck:

The idea I had (that you inspired) worked. I made another column o
sheet1
called DateCheat that contained the following formula

=if(and(Date=>'Sheet2'$A$1,Date=<'Sheet2'$B$1,Status="C"),1,0)

Then on sheet2 I put this in Cell A3 and filled right
{=sum(if((DateCheat=1)*(Name=$A2),Cost))}

and Hoseanna!!!! it worked.

Thanks for the inspiration. I was so deep in the forest I could no
see the forest for the trees.

Take Care,

Gary