2 Way Sumifs with range

[sd444]

Hi

I have a database that has products codes and product types vertically but week numbers horizontally. I want to be able to see, using a lookup cell, how many products sold between a certain range of weeks. I have been able to use a SUmIFS index Match to do the product type and single week but I cannot do the weekly range.

So for example how many DRAWS sold between Week 1 and week 3 where I can change the week with a lookup cell

FIle photo.PNG

[XLent]

I think you would need to use SUMPRODUCT given fact your criteria range & summation range are not of the same dimensions... so using your example:

Please Login or Register  to view this content.

note: A1 would need to be changed from <3 to 3.

[sd444]

Hi, that sort of works but to get a range I have to go into the formula and make the change, and the formula is < (less than)

How could I incorporate a range of weeks Week 3 to Week 5

[XLent]

have a start week cell, and end week cell then add the additional parameter to the SUMPRODUCT - e.g.:

Please Login or Register  to view this content.

where A1 is week from {inclusive}, and B1 is week to {inclusive} -- this would give you max flexibility.

in terms of range definition, you can use a Dynamic Named Range (which you should anyway if your dataset is large, even with SUMIFS etc), e.g.

Please Login or Register  to view this content.

above would create range from C3 to XY where X is last column with # in row 3, and Y is last row of text in Column C

Your SUMPRODUCT can be adapted, based on your sample data types, to :

Please Login or Register  to view this content.

in terms of using >3 as an entry value... this type of construct is really for use with COUNTIFS/SUMIFS etc - so, not really applicable... you can use it but you'd have to fudge the resulting function to strip out / evaluate the string which is really not worthwhile.


EDIT: I had a typo in the _Data SUMPRODUCT -- first criteria should have referenced column 1 rather than 3, corrected above, apologies for confusion !

[FlameRetired]

Hello sd444. Welcome to the forum.

Edited Corrected mistake in formula.

With the week numbers in C1:D1 and the Type in B1 this in A1

Please Login or Register  to view this content.

This assumes the Types are grouped as in your 1st post.

	A	B	C	D	E	F	G	H
1	1137	Draw	1	3
2
3			Type	Code	1	2	3	4
4			Draw		83	115	153	209
5			Draw		95	133	119	23
6			Draw		197	154	88	22
7			Cabinet		124	42	58	220
8			Cabinet		132	144	15	204
9			Cabinet		123	132	28	154

[sd444]

FlameRetired Thank you so much! I have been trying to get the solution to this for ages and you solved it in minutes!