• 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.

Combining SUMIF and INDEX/MATCH

J

JDC8457

Member
Each week, I have to hit a moving target. The raw data comes to me in columns with item number, date, and activity. The activity for each SKU is listed by week beginning with the first week of the year. There's a YTD Total for each item at somewhat random intervals buried in the column. Not all items will have the same number of weeks represented but each item will have a Total. So each week, the column becomes longer.
I created this formula to add activity by SKU using SUMIF (which includes the YTD Total), then deduct the TYD Total for each SKU so that I'm not double counting using INDEX/MATCH.
{(SUMIF('2014'!$A$8:$A$1000,A3,'2014'!$G$8:$G$1000)-INDEX('2014'!$G$8:$G$1000,MATCH(A3&$M$1,'2014'!$A$8:$A$1000&'2014'!$C$8:$C$1000)))}
A3 is the item number
M1="Total"
2014 Column A contains the item numbers
2014 Column C contains the individual week and YTD Total
2014 Column G contains the activity by week
For some reason, regardless of where I place this formula in my column, I get the same value, that being the number in cell G458.
I know the SUMIF portion of the formula is working correctly.
Any help is greatly appreciated.
 
I just tried copying the entire forumla down and most of the values are correct but I still have some that are pulling from cell G458. I also get a #N/A result in one cell when I know the answer should be 2,238.

Confused...
 
@JDC8457,

It's hard to say for certain, but it sounds as if your match function will return row 458 (perhaps the last row?) when no other match is found. You should evaluate each portion of your formula to ensure that it is performing as desired. The match function has an optional parameter for the type of match that is not specified by your formula. This will cause it to use the default behavior (I believe that is "less than").

Hope that helps.

Regards,
Ken
 
I know it's the INDEX/MATCH portion but I can't see the error. G458 is not in the last row.
What makes this so confusing is that the entire formula works for most cells. Why not all cells?

The MATCH takes the item number, adds "Total", then looks for the corresponding value in the raw data. I thought the errors might have something to do with using a label and tried changing Total to a value. Same result.

I'll keep experimenting. Any more thoughts are greatly appreciated.
 
Keep in mind that the match function will treat numbers and text differently; any cell that has a non-numeric character is text. When the match type criteria is not specified, the value returned will depend on what Excel interprets to be the nearest value less than the comparison value. In sort order, this will sort text after numbers.

A couple of things to try: set match type to Exact (i.e., 0); select a cell with an unexpected result and evaluate the match portion of the formula by selecting that portion in the formula bar and pressing F9. This will show you the evaluated result for that portion of your formula. PLEASE NOTE: you will need to use ESC to prevent inadvertently changing your formula.

Regards,
Ken
 
test file uploaded. Cells in column M that are green have correct values. Comments in red on right-hand side.

Thanks!
 

Attachments

  • Test file.xls
    384 KB · Views: 43
You want to exclude the total that is being added in the YTD figure? BTW if you have used the last argument of Match() as Zero you should have got Value or NA error. If the purpose of this INDEX() portion is to less the value to total that it could be done/tried upon with some other formulas as well.

Let me try...
 
@JDC8457

Try this formula instead:

=SUMPRODUCT(('2014'!$A$8:$A$539='Sales and Inventory Analysis'!A3)*('2014'!$C$8:$C$539<>"Total")*('2014'!$G$8:$G$539))
 
@JDC8457,

Please see this file, pivot table for cross checking and my workout for the results.
 

Attachments

  • Test file.xls
    502.5 KB · Views: 61
The formula works great and I understand it but I'd like to modify to to exclude data for 5/24/2014 as well as Totals. I tried adding code to accomplish this but it didn't work. Can you help?
 
Sure!!

=SUMPRODUCT(('2014'!$A$8:$A$539='Sales and Inventory Analysis'!A3)*('2014'!$C$8:$C$539<>"Total")*('2014'!C8:C539<>Myworkout!S1)*('2014'!$G$8:$G$539))

The green portion is added to check for the dates other then your specified one, Where S1 contains the date you want to exclude...
 
You must log in or register to reply here.
Back
Top