• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting Totals with Index Match

C

cmf735

Member
Hi, I'm trying to get a total inventory count with multiple batches. On Sheet1 I'm trying to get a total count of Column A in Column D. Using Sheet2 as the data the parts can count multiple times in column A. I want to take the values in Column D and add them up if the value in column A is the same to present in Column D of Sheet1. I tried to use Index/Match but that won't count the multiple lines.
 

Attachments

  • Book2.xlsx
    28.1 KB · Views: 7
Try,

In "Sheet1" D3, formula copied down :

=IF(A3="","",IFERROR(1/(1/SUMIFS(Sheet2!D:D,Sheet2!A:A,A3)),""))

Regards
 
cmf735
Could You verify - what will You count?
With Index/Match ... it's challenge to do counting.
Do You have ideas of expected results?
To use Index/Match from Pivot-table is basic as same as from any range.
What is Your point? You could even try to do it.
 
vletm said:
cmf735
Could You verify - what will You count?
With Index/Match ... it's challenge to do counting.
Do You have ideas of expected results?
To use Index/Match from Pivot-table is basic as same as from any range.
What is Your point? You could even try to do it.
Click to expand...
So if I were to do a pivot table I would want to pull the total of the Finish Goods highlighted in yellow into the Inventory column. So I would do an Index/Match to look up the numbers in the highlighted column and then pull the total inventory.
 
cmf735
Where is Your the total of the Finish Goods highlighted in yellow into the Inventory column?
As asked - Could You verify - what will You count?
As asked - Do You have ideas of expected results?
Send a sample Excel-file.
 
vletm said:
cmf735
Where is Your the total of the Finish Goods highlighted in yellow into the Inventory column?
As asked - Could You verify - what will You count?
As asked - Do You have ideas of expected results?
Send a sample Excel-file.
Click to expand...
I want the values from Sheet3 Highlighted in Green to fill the "Inventory" Column in Sheet1 by Matching the values of the 'Finished Goods' column in Sheet1 with the values from the Yellow highlighted column in Sheet3. I have 3 different Index/Match formulas I tried to use to pull that value with 1 #VALUE! error and two #N/A errors. I'm doing something wrong and I can't figure out what it is.
 
vletm said:
cmf735
Where is Your the total of the Finish Goods highlighted in yellow into the Inventory column?
As asked - Could You verify - what will You count?
As asked - Do You have ideas of expected results?
Send a sample Excel-file.
Click to expand...

Sorry looks like my file didn't attach.
 

Attachments

  • Book2.xlsx
    41.4 KB · Views: 5
cmf735
Did You recheck syntaxes?
Those are:
= INDEX(array, row_num, [column_num])
= MATCH(lookup_value, lookup_array, [match_type])
Some details which won't match with Your used formulas:
cell D3: Your index is one column width then cannot show result column values
cell D4: Your index is one cell
cell D5: Your index is result column then cannot match lookup_value
Wondering ... why Material's are text instead numbers? ... but it works this way too.
 
You must log in or register to reply here.
Back
Top