Sumif(s) based on Vertical and Horizontal Criteria

[lynxbci]

Hi,
I have a table with
Column A - Suppliers
Columns B to M products with a product group (in row 1)

Prod Group..Core.......Core........Outer........Inner.........Core
Supplier......Type A......Type B.......Type A........Type B.......Type C
AB Ltd........1000.........2000..........500.............750...........5000
CD Ltd........3000.........5000..........100.............950...........8000
AB Ltd........2000.........4000..........600..............800..........7000

I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000
(sorry didn't know how to paste a table)

Thanks in advance

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Prod Group	Core	Core	Outer	Inner	Core		Supplier	AB Ltd
2	Supplier	Type A	Type B	Type A	Type B	Type C		Prod Group	Core
3	AB Ltd	1000	2000	500	750	5000		Sum	21000
4	CD Ltd	3000	5000	100	950	8000
5	AB Ltd	2000	4000	600	800	7000
Sum

See my signature block for several ways to post small screen shots.

 

[lynxbci]

Of course, good old sumproduct, thanks so much i was over complicating and took myself down some dead ends.

thanks again

 

[AlphaFrog]

Alternatively, you could use Autofilter to select your criteria and then use the SUBTOTAL function to sum the visible data in column "Core".

 

[Peter_SSs]

Alternatively, you could use Autofilter to select your criteria and then use the SUBTOTAL function to sum the visible data in column "Core".

But note there are 3 'Core' columns that need to be combined into the sum.

 

[AlphaFrog]

I stand corrected. Obviously I didn't really look at it that closely.

 

[sskabra]

This formula does wonders for me. Been trying all the hlookup and vlookups and multiple IF loops. How does the formula execute, what exactly happens inside the formula?

 

[Robert Mika]

Use Evaluate Formula toll from Excel Option to see what's going on

 

[AlphaFrog]

This explains the SUMPRODUCT function well.

SUMPRODUCT: Multiple Condition Tests

 

[sskabra]

Thanks MrExcel MVP, AlphaFrog & Robert (Bob)...this was something new for me. Never imagined the use of sumproduct in the this fashion. Hatsoff to you guys.