calulate amount based on multi condition

[Lowan Chan]

Hi,

I have a table similar as below, how can I calculate the amount of A during
December 1 to 7? I know I can use DSUM for sigle result, but my purpose is
to consoladate a table for A, B, C. Is there any formula I can use?

coloum A coloum B coloum C
1/12/09 A 100
5/12/09 C 50
9/12/09 A 120
8/12/09 B 80
4/12/09 A 90

Thanks a lot!

 

[Mike H]

Hi,

=SUMPRODUCT((A1:A10>=DATE(2009,12,1))*(A1:A10<=DATE(2009,12,7))*(B1:B10="A")*(C1:C10))

Mike

 

[Lowan Chan]

Hi, Mike,

I tried, but doesn't work.....

I have another question, if i have all the year's data on this table, how
can i caculate the amount based on week 1, week 2, week 3...?

 

[Mike H]

Hi,

I tried, but doesn't work.....

That isn't a very helpful explanation of what went wrong but if it didn't
work then you need to check your data as follows:-

1. Are the dates in column A really dates. Try adding 1 day to one of the
dates and see what happens.

2. Are there any leading/trailing spaces in column B

3. Is column C really a number? Try adding 1 to one of the numbers to test.

As for summing by week if my first answer didn't work then this is unlikely
to but here goes.

Create a helper column with this formula in
=WEEKNUM(A1)
and drag down

To sum use this where D is the helper column and the week number you want is
49

=SUMPRODUCT((D110=49)*(B1:B10="A")*(C1:C10))

Mike

 

[Lowan Chan]

Hi Mike,
oh, sorry, it was my mistake. it works.
how about to calculate base on each week, week 1, week 2......
thanks & regards,
Lowan

 

[Mike H]

Hi,

Glad I could help, see my other post for summing by week.

Mike

 

[Lowan Chan]

Hi, Mike,

I have another problem. I found that I can use these format in excel 2007
perfectly, but not in 97-2003 version. Once I save to 97-2003, all the
result of fomula end up "#value". How can I solve this?

thanks a lot!