SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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 ***

SUMIF of adjacent variable cells
 

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 -

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.

SUMIF of adjacent variable cells
 

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.