There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.

to count bags where f6 contains "bags"
=COUNTIF(Sheet12!A:F,F6)

--
Don Guillett
SalesAid Software

"michaelberrier" wrote in message
oups.com...
There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.

Don,
Thanks for looking. This only tells me how many times that word shows
up in the list. I would like to add the numbers adjacent to that word
as they represent a quantity. I'm thinking an Offset would work as
the quantity is always the cell to the left of the value, but I'm not
sure how to write that.


On Mar 27, 10:11 am, "Don Guillett" wrote:
to count bags where f6 contains "bags"
=COUNTIF(Sheet12!A:F,F6)

--
Don Guillett
SalesAid Software
"michaelberrier" wrote in message

oups.com...

There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.

I misunderstood. Try this
=sumproduct((d3:f31="bags")*c3:c31)

--
Don Guillett
SalesAid Software

"michaelberrier" wrote in message
oups.com...
Don,
Thanks for looking. This only tells me how many times that word shows
up in the list. I would like to add the numbers adjacent to that word
as they represent a quantity. I'm thinking an Offset would work as
the quantity is always the cell to the left of the value, but I'm not
sure how to write that.


On Mar 27, 10:11 am, "Don Guillett" wrote:
to count bags where f6 contains "bags"
=COUNTIF(Sheet12!A:F,F6)

--
Don Guillett
SalesAid Software
"michaelberrier" wrote
in message

oups.com...

There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.

Assuming column A has the values & column B the description, use the
following...

=SUMIF(B:B,"Bags",A:A)

Rob Edwards

Always look on the bright side of life!

*** Sent via Developersdex http://www.developersdex.com ***

michael - i know what you want but i can't get my head around it
either, as to how to DO it.
i'm thinking your offset value idea would work, but i don't know if a
variable will hold a sum that keeps changing.......
i was waiting to see somebody more experienced than me answer it! :)
you could try something like this UNTESTED PSEUDOCODE:

sub michael()

dim myVar as variant 'might need to be integer
dim my2ndVar as variant 'same
dim rProducts as range
dim cell as range

set rProducts= whatever
set myVar = 0
for each cell in rproducts
if cell.text= "boxes" then
set my2ndVar as offset(0,-1).value
myvar = myvar + my2ndvar
end if
next cell

end sub

maybe this helps?
:)
susan


On Mar 27, 10:32 am, "michaelberrier"
wrote:
Don,
Thanks for looking. This only tells me how many times that word shows
up in the list. I would like to add the numbers adjacent to that word
as they represent a quantity. I'm thinking an Offset would work as
the quantity is always the cell to the left of the value, but I'm not
sure how to write that.

On Mar 27, 10:11 am, "Don Guillett" wrote:



to count bags where f6 contains "bags"
=COUNTIF(Sheet12!A:F,F6)

--
Don Guillett
SalesAid Software
"michaelberrier" wrote in message

roups.com...

There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.- Hide quoted text -

- Show quoted text -

These are great answers, but I may have been vague about the sources
of my data.

The values come from several lists that are within the range D3:F31.
In other words, one list may be from D3:D15 with the corresponding
quantities in column C. Another list may be from F4:F31 with the
quantities in E. How can I pick the values out from these different
places and sum them beside a master list either below or on the same
sheet?

Thanks again.

okay.........
the only thing i can come up with is to have formulas @ the top or
bottom of each range, doing the =sumif thing for each possible entry.
then have a master summary sheet that adds all the cells on the other
worksheet(s) for each entry.

i.e., d3:d15 =SUMIF(c:c,"Bags",d:d)
g3:g45 =SUMIF(f:f,"Bags",g:g)
r3:r10 =SUMIF(q:q,"Bags",r:r)

then the summary sheet would have =sum(1st sumif BAGS cell)+(2nd sumif
BAGS cell)+(3rd sumif BAGS cell)

susan



On Mar 27, 12:55 pm, "michaelberrier"
wrote:
These are great answers, but I may have been vague about the sources
of my data.

The values come from several lists that are within the range D3:F31.
In other words, one list may be from D3:D15 with the corresponding
quantities in column C. Another list may be from F4:F31 with the
quantities in E. How can I pick the values out from these different
places and sum them beside a master list either below or on the same
sheet?

Thanks again.

You don't need to have multiple formulas:
assume
your data is in Sheet1 in D3:F31 as you describe with text in columns D and
F, numbers in C and E

in sheet2, assume bags is in A2 and other keywords in A3 on down

in B2
=SUMIF(Sheet1!$D$3:$F$31,A2,Sheet1!$C$3:$E$31)
then drag fill down the column

pay attention to the how the ranges are entered. Works fine for me using
the description you provided - but I can't see your data. Give it a try.

--
Regards,
Tom Ogilvy



"Susan" wrote in message
oups.com...
okay.........
the only thing i can come up with is to have formulas @ the top or
bottom of each range, doing the =sumif thing for each possible entry.
then have a master summary sheet that adds all the cells on the other
worksheet(s) for each entry.

i.e., d3:d15 =SUMIF(c:c,"Bags",d:d)
g3:g45 =SUMIF(f:f,"Bags",g:g)
r3:r10 =SUMIF(q:q,"Bags",r:r)

then the summary sheet would have =sum(1st sumif BAGS cell)+(2nd sumif
BAGS cell)+(3rd sumif BAGS cell)

susan



On Mar 27, 12:55 pm, "michaelberrier"
wrote:
These are great answers, but I may have been vague about the sources
of my data.

The values come from several lists that are within the range D3:F31.
In other words, one list may be from D3:D15 with the corresponding
quantities in column C. Another list may be from F4:F31 with the
quantities in E. How can I pick the values out from these different
places and sum them beside a master list either below or on the same
sheet?

Thanks again.

Assume for demo

Sheet1:

Column A, Column B

5 Bags
3 Boxes
2 Carts
1 Bags
7 Boxes
3 Carts

Sheet2

Column A ColumnB
Boxes =Sumif(Sheet1!B:B,A1,Sheet1!A:A) in row 1
Bags =Sumif(Sheet1!B:B,A2,Sheet1!A:A) in row 2
carts =Sumif(Sheet1!B:B,A3,Sheet1!A:A) in row 3

--
Regards,
Tom Ogilvy


"michaelberrier" wrote:

There are several lists of items in the range D3:F31. In the same
range, beside the items are quantities, like so

5 Bags
3 Boxes
2 Carts etc,

Many of the items are duplicated throughout the multiple lists, so I'd
like a total quantity of each item. I've copied all the items to a
master list, but I'm stumped on the function to pull the individual
values out of each list for each item...I'm guessing it's a SUMIF, but
I'm not sure how to make it work.

Thanks in advance.