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

Using SUM or SUMIF with INDEX/MATCH

J

JDC8457

Member
I attached a raw data sample.

The goal is to be able to create a table with store number in column A and the sales of individual items by year for each store across the rows. I will change the formula to capture the correct sales column...but need to have a working formula first!

My attempt at using sumif with index match didn't give me the accurate counts. Yes, the raw data in my actual sheet is 20000 rows deep.

=sumif(INDEX(Sales!$J$3:$J$20000,MATCH($A11&$E$8,Sales!$A$3:$A$20000&Sales!$C$3:$C$20000,0)),"")

$A11 is the store number
$e$8 is the item number
"Sales" column A contains the store number
"Sales" column C contains the item number
"Sales" column J is where the values are located that need to be added.

Many thanks for the help.
 

Attachments

  • Chandoo.xlsx
    11.5 KB · Views: 4
Your sample does not match your description.

I'd recommend uploading raw data, along with manually created expected output.

Also, I'd recommend using PivotTable to summarize this sort of data. Much faster and more robust than formula in most cases.
 
The original raw data file is too large to attach thus the abbreviated raw data attachment.

The formula would be used on my summary sheet thus the references to $a11 and $e$8.

Yeah, thought about a pivot table but the targeted recipient of my labors wants the summary data in a table.

Any help you can provide will be appreciated enormously.
 
You must log in or register to reply here.
Back
Top