| View previous topic :: View next topic |
| Author |
Message |
James Klug
General User
Joined: 20 Nov 2004
Posts: 24
|
 Posted: Mon Nov 29, 2004 9:41 am Post subject: filtering information and adding |
 |
|
I have information in sheet that i would like to filter and post totals in sheet b:
Lets say my sheet looks like this:
D(block#) F(species) J(cords)
6853 spruce 21.32
6855 poplar 22.44
6855 tamarack 20.22
6853 spruce 22.33
on sheet b I would like to organize the information so that it will give me totals of specific blocks( column d).
How do I filter the information so that on sheet b it will give me total of species for each block?
for example I need to know the cordage total of spruce that was in block 6853.
to complicate things further I also need a formula that will total all the cordage for the entire block so that when I enter 6855 in d and 20.22 in j it will know to add 20.22 cords to the block 6855 cordage total on sheet b.
thanks in advance. |
|
| Back to top |
|
 |
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
| Posted: Tue Nov 30, 2004 1:00 am Post subject: Re: filtering information and adding |
|
|
| James Klug wrote: | I have information in sheet that i would like to filter and post totals in sheet b:
Lets say my sheet looks like this:
D(block#) F(species) J(cords)
6853 spruce 21.32
6855 poplar 22.44
6855 tamarack 20.22
6853 spruce 22.33
on sheet b I would like to organize the information so that it will give me totals of specific blocks( column d).
How do I filter the information so that on sheet b it will give me total of species for each block?
for example I need to know the cordage total of spruce that was in block 6853.
to complicate things further I also need a formula that will total all the cordage for the entire block so that when I enter 6855 in d and 20.22 in j it will know to add 20.22 cords to the block 6855 cordage total on sheet b.
thanks in advance. |
This looks like a job for: SUMIF Function...
This is how I plan do it, maybe there is something better
1) You would have another column in your "data" sheet that concatenate column d and f.
Ex. on Column A1 type =D1&F1, then drag-fill down remaining rows for columnA
2) Then on sheet b the formula would be:
=SUMIF(Sheet1.A1:A1000;"6853" & "spruce";Sheet1.J1:J1000)
Note:
"6853" & "spruce" may be replaced with a cell reference
_________________
# : - ) |
|
| Back to top |
|
|
James Klug
General User
Joined: 20 Nov 2004
Posts: 24
|
| Posted: Tue Nov 30, 2004 6:42 am Post subject: |
|
|
| thanks I'll giveit a try |
|
| Back to top |
|
|
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
| Posted: Tue Nov 30, 2004 4:58 pm Post subject: |
|
|
| James Klug wrote: | | thanks I'll giveit a try |
Yes. Please let me know if it works (or if there are some problems).
_________________
# : - ) |
|
| Back to top |
|
|
|
