I am trying to create a cell that will count the number of widgets it finds
in a given sheet or multiple sheets, based on their name and the quantity
that is marked in an adjacent cell. I would like to have it reported at the
bottom of the sheet or better yet on a totals sheet that would have all of
the different types of widjets accounted for in their own special places.

I am using office 2007

I am greatful for any insight you may have.
--
Thank you

Eric

Assuming you have column A with the names
A
1) Widget 1
2) Widget 2
3) Widget 1

and the amounts in column B
B
1) 10
2) 20
3) 10

You can use the SUMIF formula in either column C or in your totals tab
=SUMIF($A$1:$A$3,A1,$B$1:$B$3)
and drag down the formula as needed. A1 represents the type of widget you
want to sum over. You can also of course hard code this into the formula by
replacing it with "Widget 1" Or if you want to get rid of the duplicates you
can first copy over a list of unique values in the in widget list by
highlighting the column with the widget name information and go to...
Data - Filter - Advanced Filter
= click "unique records only" and "copy to another location"
then list the cell you want the unique list to start in and use that for the
2nd criteria. Drag down your formula for as many of the widget names that
you have. Hope this helps.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"EAE" wrote:

I am trying to create a cell that will count the number of widgets it finds
in a given sheet or multiple sheets, based on their name and the quantity
that is marked in an adjacent cell. I would like to have it reported at the
bottom of the sheet or better yet on a totals sheet that would have all of
the different types of widjets accounted for in their own special places.

I am using office 2007

I am greatful for any insight you may have.
--
Thank you

Eric

Michelle, thanks for your help.

although my expectations are high, my skill is low.

My situations is something like this.

1)Area Light Fixture Type Qty
2)2nd Floor bed Ceiling Mount Av. 1
3)2nd Floor closet Ceramic 2
4)2nd floor hall Celing mount Av. 2
5)Attic Ceramic 3
6)Marty's Office Flourescent 4' 1

The light fixture types are drop downs that i made and are the Text examples
that i want to total based on the quantity cells. I want to be able to
organize this with different texts in column b (as in example) and have
several "total" cells at the end of this sheet or better yet on a summary
sheet that will find each example of say Ceramic or ceiling mount av and
tally the corresponding quantities for each type.
something like this.


Ceramic 15
Ceiling Mount av 22
Flourescent 4' 6


I have been told that i ask for too much from people before in regards to
excel but wonder if this can be done.


--
Thank you

Eric


"Michelle" wrote:

Assuming you have column A with the names
A
1) Widget 1
2) Widget 2
3) Widget 1

and the amounts in column B
B
1) 10
2) 20
3) 10

You can use the SUMIF formula in either column C or in your totals tab
=SUMIF($A$1:$A$3,A1,$B$1:$B$3)
and drag down the formula as needed. A1 represents the type of widget you
want to sum over. You can also of course hard code this into the formula by
replacing it with "Widget 1" Or if you want to get rid of the duplicates you
can first copy over a list of unique values in the in widget list by
highlighting the column with the widget name information and go to...
Data - Filter - Advanced Filter
= click "unique records only" and "copy to another location"
then list the cell you want the unique list to start in and use that for the
2nd criteria. Drag down your formula for as many of the widget names that
you have. Hope this helps.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"EAE" wrote:

I am trying to create a cell that will count the number of widgets it finds
in a given sheet or multiple sheets, based on their name and the quantity
that is marked in an adjacent cell. I would like to have it reported at the
bottom of the sheet or better yet on a totals sheet that would have all of
the different types of widjets accounted for in their own special places.

I am using office 2007

I am greatful for any insight you may have.
--
Thank you

Eric

Eric,

I'm not sure I entirely understand your example below. Your totals don't
seem to add up, and I'm not sure I understand what you mean by "The light
fixture types are drop downs that i made and are the Text examples that i
want to total based on the quantity cells." However, I'm going to give you
what I think you want. (Please note that the "#)" represent the row numbers.)

A
1) Area Light Fixture Type
2) 2nd Floor Bed Ceiling Mount Av.
3) 2nd Floor Closet Ceramic
4) 2nd Floor Hall Ceiling Mount Av.
5) Attic Ceramic
6) Marty's Office Floerescent 4'

B
1) Qty
2) 1
3) 2
4) 2
5) 3
6) 1

The next column is the text that you want to look for within these strings
C
1) Ceramic
2) Ceiling Mount
3)Flourescent 4'

D (formulas)
1) =SumIfString(C2, $A$2:$A$6, $B$2:$B$6)
2) =SumIfString(C3, $A$2:$A$6, $B$2:$B$6)
3) =SumIfString(C4, $A$2:$A$6, $B$2:$B$6)

D (results)
1) 5
2) 3
3) 1

Where the SumifString function is defined via VBA in a module in the
workbook as follows:

Function SumIfString(stringCheck As Range, descrRange As Range, sumRange As
Range)
Dim i As Range

SumIfString = 0
j = 1
For Each i In descrRange
If InStr(1, i.Value, stringCheck.Value) 0 Then
SumIfString = SumIfString + sumRange.Cells(j).Value
End If
j = j + 1
Next i
End Function

(I'm hoping the word wrap on here didn't do funny things to my lines of
code.) Please note that this is a case dependent function. Although I have
the formulas in col D of the same worksheet you can put the function anywhere
in the worksheet that you like, and it will still work. In case you don't
know how to add the VBA code I have set up above... Tools = Macro = Visual
Basic Editor... Once there you can go to Insert = Module and add this
functions code in your new module. Hopefully this gets you a little closer
to your answer.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"EAE" wrote:

Michelle, thanks for your help.

although my expectations are high, my skill is low.

My situations is something like this.

1)Area Light Fixture Type Qty
2)2nd Floor bed Ceiling Mount Av. 1
3)2nd Floor closet Ceramic 2
4)2nd floor hall Celing mount Av. 2
5)Attic Ceramic 3
6)Marty's Office Flourescent 4' 1

The light fixture types are drop downs that i made and are the Text examples
that i want to total based on the quantity cells. I want to be able to
organize this with different texts in column b (as in example) and have
several "total" cells at the end of this sheet or better yet on a summary
sheet that will find each example of say Ceramic or ceiling mount av and
tally the corresponding quantities for each type.
something like this.


Ceramic 15
Ceiling Mount av 22
Flourescent 4' 6


I have been told that i ask for too much from people before in regards to
excel but wonder if this can be done.


--
Thank you

Eric

I goofed my C & D cols. It should have been...

C
1)
2) Ceramic
3) Ceiling Mount
4) Floureescent 4'

D (formulas)
1)
2) =SumIfString(C2, $A$2:$A$6, $B$2:$B$6)
3) =SumIfString(C3, $A$2:$A$6, $B$2:$B$6)
4) =SumIfString(C4, $A$2:$A$6, $B$2:$B$6)

D (results)
1)
2) 5
3) 3
4) 1

Sorry about that.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"Michelle" wrote:

Eric,

I'm not sure I entirely understand your example below. Your totals don't
seem to add up, and I'm not sure I understand what you mean by "The light
fixture types are drop downs that i made and are the Text examples that i
want to total based on the quantity cells." However, I'm going to give you
what I think you want. (Please note that the "#)" represent the row numbers.)

A
1) Area Light Fixture Type
2) 2nd Floor Bed Ceiling Mount Av.
3) 2nd Floor Closet Ceramic
4) 2nd Floor Hall Ceiling Mount Av.
5) Attic Ceramic
6) Marty's Office Floerescent 4'

B
1) Qty
2) 1
3) 2
4) 2
5) 3
6) 1

The next column is the text that you want to look for within these strings
C
1) Ceramic
2) Ceiling Mount
3)Flourescent 4'

D (formulas)
1) =SumIfString(C2, $A$2:$A$6, $B$2:$B$6)
2) =SumIfString(C3, $A$2:$A$6, $B$2:$B$6)
3) =SumIfString(C4, $A$2:$A$6, $B$2:$B$6)

D (results)
1) 5
2) 3
3) 1

Where the SumifString function is defined via VBA in a module in the
workbook as follows:

Function SumIfString(stringCheck As Range, descrRange As Range, sumRange As
Range)
Dim i As Range

SumIfString = 0
j = 1
For Each i In descrRange
If InStr(1, i.Value, stringCheck.Value) 0 Then
SumIfString = SumIfString + sumRange.Cells(j).Value
End If
j = j + 1
Next i
End Function

(I'm hoping the word wrap on here didn't do funny things to my lines of
code.) Please note that this is a case dependent function. Although I have
the formulas in col D of the same worksheet you can put the function anywhere
in the worksheet that you like, and it will still work. In case you don't
know how to add the VBA code I have set up above... Tools = Macro = Visual
Basic Editor... Once there you can go to Insert = Module and add this
functions code in your new module. Hopefully this gets you a little closer
to your answer.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx