M
Majal
Hi Ray,
Use the SUMIF function:
=SUMIF(A:A, "a", C:C)
Have a great day!
Use the SUMIF function:
=SUMIF(A:A, "a", C:C)
Have a great day!
W
wjay29
Try the SUMIF function. It worked for me.
C
Cstep
Is there a way to drag this formula and not enter the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER) for every row?
This formula works great for my worksheet but I need to drag it down 10,000+
rows?
Reference formula:
=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data!B1:B10=D1),0)).
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER) for every row?
This formula works great for my worksheet but I need to drag it down 10,000+
rows?
Reference formula:
=INDEX(Data!C1:C10,MATCH(1,(Data!A1:A10=A1)*(Data!B1:B10=D1),0)).
V
vas
Hi
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty Item Stock
1-Jan a 12 a 14
4-Mar b 13 b 15
4-May c 14 c 16
7-Sep d 15 d 17
8-Mar e 16 e 18
9-Aug f 17 f 19
12-Dec g 17 g 19
12-Dec a 1
Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty Item Stock
1-Jan a 12 a 14
4-Mar b 13 b 15
4-May c 14 c 16
7-Sep d 15 d 17
8-Mar e 16 e 18
9-Aug f 17 f 19
12-Dec g 17 g 19
12-Dec a 1
Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this
V
vas
Hi
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17
Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19
Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this.
when i enter an item the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17
Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19
Waiting for the suggestions. we cannot use the more than 7 nested loops. So
how will we solve this.
V
vas
Hi
The equation
if item_a=stockitem_a
(stockqty_a=stockqty_a-itemqty_a)
else if item_b=stockitem_b
(stockqty_b=stockqty_b-itemqty_b)
....
but more than 7 nested loops are not applicable. so please suggest.
....
when i enter an item, the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...
eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17
Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19
Waiting for the suggestions. we cannot use the more than 7 nested loops.
waiting for the solution with thanks
The equation
if item_a=stockitem_a
(stockqty_a=stockqty_a-itemqty_a)
else if item_b=stockitem_b
(stockqty_b=stockqty_b-itemqty_b)
....
but more than 7 nested loops are not applicable. so please suggest.
....
when i enter an item, the stock should be reduced by the quantity and whn i
enter the same item quantity again then stock should be reduced from the
remaining quantity. how will i do that...
eg item a is entered twice 1st 12
quantity, so the stock should be 2 and i entered a again in the last
1quantity. so the remaining stock should be 1
Date Item Qty
1-Jan a 12
4-Mar b 13
4-May c 14
7-Sep d 15
8-Mar e 16
9-Aug f 17
12-Dec g 17
Item Stock
a 14
b 15
c 16
d 17
e 18
f 19
g 19
Waiting for the suggestions. we cannot use the more than 7 nested loops.
waiting for the solution with thanks
D
Don Guillett
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
T
teylyn
Hi,
you could combine the starting balance of Item Stock with a SUMIF(
formula. For example, if Item a has a starting stock of 14, put thi
formula in the Stock sheet for Item a:
=14-(SUMIF(Data!$B$2:$B$100,"a",Data!$C$2:$C$100)
where column B is the Item and column C is the turnover on the Dat
sheet.
hth
teylyn
you could combine the starting balance of Item Stock with a SUMIF(
formula. For example, if Item a has a starting stock of 14, put thi
formula in the Stock sheet for Item a:
=14-(SUMIF(Data!$B$2:$B$100,"a",Data!$C$2:$C$100)
where column B is the Item and column C is the turnover on the Dat
sheet.
hth
teylyn
D
DP
The original question is close to what I'm looking for. I have two pages in
a workbook - SOURCE & DATA. SOURCE has a single column of information.
DATA has multiple columns, can match SOURCE on column 1, but also have
multiple matches for each.
SOURCE Worksheet
Col A
1 abc
2 def
3 ghi
4 jkl
5 mno
DATA Worksheet
Col A B C
1 ghi 12 1000 E. Main
2 mno 09 12W23S Western
3 abc 43 400 3A Crawford
4 abc 05 1313 Mockingbird Lane
5 mno 23 666 Damian Way
Doing a normal VLookup (in the SOURCE worksheet) would return the first
match (SOURCE Row 1, to DATA Row 3). I need to write the formula so that it
returns DATA Columns A, B, C for Rows 3 and 4.
SOURCE
Row 2 = 0 matches
Row 3 = 1 match
Row 4 = 0 matches
Row 5 = 2 matches
Please advise. Thank you!
a workbook - SOURCE & DATA. SOURCE has a single column of information.
DATA has multiple columns, can match SOURCE on column 1, but also have
multiple matches for each.
SOURCE Worksheet
Col A
1 abc
2 def
3 ghi
4 jkl
5 mno
DATA Worksheet
Col A B C
1 ghi 12 1000 E. Main
2 mno 09 12W23S Western
3 abc 43 400 3A Crawford
4 abc 05 1313 Mockingbird Lane
5 mno 23 666 Damian Way
Doing a normal VLookup (in the SOURCE worksheet) would return the first
match (SOURCE Row 1, to DATA Row 3). I need to write the formula so that it
returns DATA Columns A, B, C for Rows 3 and 4.
SOURCE
Row 2 = 0 matches
Row 3 = 1 match
Row 4 = 0 matches
Row 5 = 2 matches
Please advise. Thank you!
S
Sarah
Does anyone know if you can combine and IF, AND, and VLOOKUP. I need to
lookup 2 different dates in a table and if both of those dates appear on the
one sheet, I need the associated value to be returned. If this is possible,
how would I do this?
lookup 2 different dates in a table and if both of those dates appear on the
one sheet, I need the associated value to be returned. If this is possible,
how would I do this?
B
Bob Phillips
Use this array formula
=INDEX(rng3,MATCH(1,(rng1=condition1)*(rng2=condition2),0))
=INDEX(rng3,MATCH(1,(rng1=condition1)*(rng2=condition2),0))
M
Minnie
I had a question re duplicates as well:
I am trying to do a vlookup matching number figurers to their corresponding
year. However the years are duplicated:
Jan 1990 5555
feb 1990 4555
Mar 1990 4444
Jan 1991 54487
Feb 1991 2255
And so on...
i have a separate table with just the numbers. I am trying to match the
year to the numbers in the other table but am uable to use the vlookp due to
the duplicates.
Suggestions would be great!
Thanks!
I am trying to do a vlookup matching number figurers to their corresponding
year. However the years are duplicated:
Jan 1990 5555
feb 1990 4555
Mar 1990 4444
Jan 1991 54487
Feb 1991 2255
And so on...
i have a separate table with just the numbers. I am trying to match the
year to the numbers in the other table but am uable to use the vlookp due to
the duplicates.
Suggestions would be great!
Thanks!
T
The Rimalaya
T. Valko said:If your data table is sorted or grouped together as is shown in your sample:
A210 = data table
F2 = lookup value = 34377007
G2 = instance number = 2
=INDEX(D2
Result = 5313312
Cann't we do the same thing, if the data are not sorted... ??
G
Gemini...JV
I am having trouble with the following:
I have a sheet sorted in ID order and they have reported months next to them
(therefore there are duplicates ID).
Data source
2138 january 147
2138 february 161
2138 may 112
2138 june 191
2384 january 118
2384 february 119
New report
January february march april
2138
2384
I want to find a fuction where I need the 3rd column from source with ID and
Month matching.
Can you please help?
I have a sheet sorted in ID order and they have reported months next to them
(therefore there are duplicates ID).
Data source
2138 january 147
2138 february 161
2138 may 112
2138 june 191
2384 january 118
2384 february 119
New report
January february march april
2138
2384
I want to find a fuction where I need the 3rd column from source with ID and
Month matching.
Can you please help?
G
G.
Hi I actually have another similar problem, can you help me?
Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column. Please
help!
1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888
Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column. Please
help!
1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888
O
ozgrid.com
Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
G
G.
Hi,
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?
Thanks,
G.
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?
Thanks,
G.
R
Roger Govier
Hi
Drag B to the Column area
Drag B to the Column area