Need SUMIF / SUMPRODUCT help I think

  • Thread starter Thread starter Big_Tater
  • Start date Start date
B

Big_Tater

After searching for hours I have resulted to posting a question. Sorry if
this is a duplicate but couldn't find what I needed.

I have a very large range of data I need to pull apart to get a breakdown of
totals from. I am pulling this to a different page and then converting to
values so I can delete the data. I do this every day to track daily sales. (I
will say I am not familiar with macros at all.)

(Data example below)

Company A Company B Company C through Col GI
Col A Col B Col C Col D Col E Col F
Item Value Item Value Item Value
1E $2 1W $5 3E $3
5W $2 3E $5 5E $3
3E $2 6W $5 4W $3
1E $2 2W $5 5E $3

through row 3000

I have a range name set as "REV_DATA" to cover A4:GI3000 already.

So each letter number combination represents an item sold for the company in
the column its under. On my other tab/worksheet I am totaling how much
revenue was brought in for that item sold across all companies. (This is also
stored as how much was sold for a specific company so I can't do away with my
above structure.)

From the example above:
1E $4
1W $5
3E $10

The only formula I can come up with would be for each item (900 individual
items). In a perfect world a formula that would use my range (REV_DATA) to
pull the individual items sold for how much would be great.

Any help would be greatly appreciated and would save me HOURS of work.
Thanks.
 
This formula is sized for your example (A3 to F6)

Say you have a list of items starting in H1 down.
enter this in G1, and copy down as needed:

=SUMPRODUCT(--(A$3:E$6=H1),B$3:F$6)

Note that you go from columns A to E, then from B to F!
 
Awesome! Thanks so much for the help. That does the trick.

This forum is so helpful and I am extremely greatful.
Thanks RagDryer!
 
Back
Top