Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
I have a spreadsheet that uses visual basics to place info in to different
worksheets. What formula do I use to sort by 2 different columns and then add a third? I need to sperate the data more to create different graphs. Example Dogs Date # pups Lab 5/2 3 Lab 5/2 2 Beagle 5/2 5 Find Total "# pups" from "labs" on "5/2" Hope someone understands what I am looking for. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
Hi
Firstly Copy your table from A1 Go to cell# D2 and paste this formula and press Ctrl+Shift+Enter =SUM(IF($B$2:$B$4=$B2,IF($A$2:$A$4=$A2,$C$2:$C$4,0 ),0)) This is an conditional formula which you can find by yourself at Menu/tools/conditional sum. you can also type in Lab in cell A6 and date 5/2 in b6 cell... then copy the formula below in cell C6. =SUM(IF($B$2:$B$4=$B6,IF($A$2:$A$4=$A6,$C$2:$C$4,0 ),0)) Do the same thing... it's and array formula so you have to use ctrl+shift+enter to get the result... Now if you want to creat the graph... it's way easier using the pivot graphs or even to classify the data use the pivot tables... it's way easier than giving the conditional sum... Guess it will help Ciao Anant "Scrappy" wrote: I have a spreadsheet that uses visual basics to place info in to different worksheets. What formula do I use to sort by 2 different columns and then add a third? I need to sperate the data more to create different graphs. Example Dogs Date # pups Lab 5/2 3 Lab 5/2 2 Beagle 5/2 5 Find Total "# pups" from "labs" on "5/2" Hope someone understands what I am looking for. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
I tried it and got a #Value! error. Could the cause be that the 1st row the
data is entered in I did a Ctrl/Shift/arrowup so the old data moves a row down when new data is entered? "ART" wrote: Hi Firstly Copy your table from A1 Go to cell# D2 and paste this formula and press Ctrl+Shift+Enter =SUM(IF($B$2:$B$4=$B2,IF($A$2:$A$4=$A2,$C$2:$C$4,0 ),0)) This is an conditional formula which you can find by yourself at Menu/tools/conditional sum. you can also type in Lab in cell A6 and date 5/2 in b6 cell... then copy the formula below in cell C6. =SUM(IF($B$2:$B$4=$B6,IF($A$2:$A$4=$A6,$C$2:$C$4,0 ),0)) Do the same thing... it's and array formula so you have to use ctrl+shift+enter to get the result... Now if you want to creat the graph... it's way easier using the pivot graphs or even to classify the data use the pivot tables... it's way easier than giving the conditional sum... Guess it will help Ciao Anant "Scrappy" wrote: I have a spreadsheet that uses visual basics to place info in to different worksheets. What formula do I use to sort by 2 different columns and then add a third? I need to sperate the data more to create different graphs. Example Dogs Date # pups Lab 5/2 3 Lab 5/2 2 Beagle 5/2 5 Find Total "# pups" from "labs" on "5/2" Hope someone understands what I am looking for. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas
The formula is an Array formula and this is a sample formula for your question.
SO what I said earlier is create the same data in your question from Cell A1 with the headings to it. i.e. the headings like dog, date and pups will come in row # 1. When you try as per the instructions and hit enter on the formula just use ctrl+shift+enter in place of only enter. Once you get the value there then you can update the foumula to as much as data you want. Remember ctrl+shift+enter. Guess that will help... Or send me your email address I will send you the file. Ciao Anant "Scrappy" wrote: I tried it and got a #Value! error. Could the cause be that the 1st row the data is entered in I did a Ctrl/Shift/arrowup so the old data moves a row down when new data is entered? "ART" wrote: Hi Firstly Copy your table from A1 Go to cell# D2 and paste this formula and press Ctrl+Shift+Enter =SUM(IF($B$2:$B$4=$B2,IF($A$2:$A$4=$A2,$C$2:$C$4,0 ),0)) This is an conditional formula which you can find by yourself at Menu/tools/conditional sum. you can also type in Lab in cell A6 and date 5/2 in b6 cell... then copy the formula below in cell C6. =SUM(IF($B$2:$B$4=$B6,IF($A$2:$A$4=$A6,$C$2:$C$4,0 ),0)) Do the same thing... it's and array formula so you have to use ctrl+shift+enter to get the result... Now if you want to creat the graph... it's way easier using the pivot graphs or even to classify the data use the pivot tables... it's way easier than giving the conditional sum... Guess it will help Ciao Anant "Scrappy" wrote: I have a spreadsheet that uses visual basics to place info in to different worksheets. What formula do I use to sort by 2 different columns and then add a third? I need to sperate the data more to create different graphs. Example Dogs Date # pups Lab 5/2 3 Lab 5/2 2 Beagle 5/2 5 Find Total "# pups" from "labs" on "5/2" Hope someone understands what I am looking for. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |