summary with multi condition
Posted by lyvia chung on January 22, 2002 1:40 AM
Dear Sir,
The question is about how to setup the summary formula with multi condition.
There is add-in function call "conditional sum wizard".
After install the function. I can setup the summary formula with multi condition.
Because there are many cell need to setup, and I just tried to copy the formula to other cell and changed the formula. But it can't work.
Help!! Help!! Pls. have someone to help me.
Posted by Aladin Akyurek on January 22, 2002 1:58 AM
Lyvia --
Care to post the formula you created with the wizard?
Aladin
========
Posted by lyvia chung on January 22, 2002 5:56 PM
Aladin,
Thanks for your quickly response.
The excel file data sheet is as below:
Collum A - Sales
Collum B - Grade
Collum C - Jan.
Collum D - Feb.
Collum E - Mar.
The result worksheet will be
Collum A - Sales
Collum B - Jan.
Collum C - Feb.
Collum D - Mar.
Then I use the wizard "condition sum wizard" to create cell C1 value.
The formula will be
=SUM(IF(Sheet1!$A$2:$A$13="A",IF(Sheet1!$B$2:$B$13=100,Sheet1!$C$2:$C$13,0),0))
I'd like to copy the C1 formula to D1.
And changed "Sheet1!$C$2:$C$13" to "Sheet1!$D$2:$D$13",
But it can't work.
I also tried to type the formula manually not use the wizard. But it's also not work.
Posted by Aladin Akyurek on January 22, 2002 6:32 PM
Lyvia --
Would you do the following?
Activate an empty cell in Sheet1 outside of your data, type in this cell =, select A1:D5, and hit F9. Go to the Formula Bar, copy what you see, and paste it in the follow up posting.
If what you have is sensitive, make a copy of sheet1 and change things a bit and then follow the above procedure.
Aladin
=========
Posted by lyvia chung on January 22, 2002 7:05 PM
Aladin,
Pls. find the information as below for the detail data.
{"Sales","Grade","Jan.","Feb. ";"A",100,150,200;"A",200,100,200;"A",100,150,200;"A",100,105,100}
Posted by Aladin Akyurek on January 22, 2002 7:51 PM
Lyvia --
There are other ways to accomplish what you want, e.g., PivotTables.
What follows is a formula-based system that produces a summary the way you want to have.
In Sheet2,
in A2 enter: Grade
in B2 enter: 100
in A4:D4 enter: {"Sales","Jan.","Feb.","Mar."} [ You know by know what this means. ]
in A5 enter: A
in B5 enter: =SUMPRODUCT((Sheet1!$A$2:$A$5=$A5)*(Sheet1!$B$2:$B$5=$B$2),Sheet1!C$2:C$5)
You can simply copy this formula across for all months.
===========
Posted by lyvia chung on January 22, 2002 9:42 PM
Aladin,
Thanks. It works.
Lyvia
