calulate amount based on multi condition
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((D1:D10=49)*(B1:B10="A")*(C1:C10))
Mike
"Lowan Chan" wrote:
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...?
--
Lowan
"Mike H" wrote:
Hi,
=SUMPRODUCT((A1:A10=DATE(2009,12,1))*(A1:A10<=DAT E(2009,12,7))*(B1:B10="A")*(C1:C10))
Mike
"Lowan Chan" wrote:
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!
--
Lowan
|