SUMPRODUCT - SUM multiple range with Multiple Column Criteria

[dluhut]

Country	Sales Person	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Canada	John	Milk	1	2	3	4	5	6	7	8	9	10	11	12
Canada	John	Fruit	2	2	3	3	4	4	5	5	6	6	7	7
Canada	John	Meat	11	21	31	41	51	61	71	81	91	101	111	121
USA	John	Milk	21	22	23	24	25	26	27	28	29	210	211	212

Given the table above, how do I make a SUMPRODUCT formula that sums 'Jan' to 'Dec' dynamically based on other columns criteria.

i.e. There's input somewhere on Excel cells for
From Month = "May"
End Month = "Oct"
Country = Canada
Sales Person = "John"
Product = "Meat"

Given the criteria above, my SUMPRODUCT result should be 456.

I tried the formula below but it's not giving me any value.

Please Login or Register  to view this content.

FYI...the actual file that I've created, the range is actually a table...however, the above formula that I've created is a test to see if I can sum multiple range with multiple column criteria.

Please Login or Register  to view this content.

[Fluff13]

As long as your months are actual dates, rather than text, try

Please Login or Register  to view this content.

Excel 2013 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Country	Sales Person	Product	01/01/2019	01/02/2019	01/03/2019	01/04/2019	01/05/2019	01/06/2019	01/07/2019	01/08/2019	01/09/2019	01/10/2019	01/11/2019	01/12/2019
2	Canada	John	Milk	1	2	3	4	5	6	7	8	9	10	11	12
3	Canada	John	Fruit	2	2	3	3	4	4	5	5	6	6	7	7
4	Canada	John	Meat	11	21	31	41	51	61	71	81	91	101	111	121
5	USA	John	Milk	21	22	23	24	25	26	27	28	29	210	211	212
6
7
8
9
10	01/05/2019		456
11	01/10/2019
12	Canada
13	John
14	Meat

Sheet: Data

[dluhut]

Hi Fluff13...the months are actually 'Text' and they're going across the columns.

Just as how I've created above.

PS: How did you create such a beautiful table?

[Fluff13]

The table is courtesy of Forum Tools available here https://www.dropbox.com/s/umis204g6e...ools.xlam?dl=0
If you sue it, then you need to select the BB code option, rather then the HTML option.

I see that you edited you post as I posted.
As the months are text, it's beyond my knowledge of formulae, so hopefully somebody else will step in.

[dluhut]

I was able to have the formula to work using Table.

And the solution is at the thread.

And thanks Fluff13 for the information on the BB code option. Will definitely try it.

[dluhut]

Am curious to know, how would the formula be if I'm not using Table but range instead, and that the month are indeed text.

[Fluff13]

Just figured it out, as a table you can use

Please Login or Register  to view this content.

Or as normal data

Please Login or Register  to view this content.