Multiple Sumproduct issue, using rows and columns

[bat18]

I have a tricky question which i can't get my head round and was hoping for some assistance.

i need to do a sumif on multiple criteria (so a sumproduct is needed but it is causing issues)

I need it to sum on the following criteria

1. Cell B1 = something in column C
2. Cell D3 = something in row 4 (E4:FF4)
3. It only sums the first x amount of times that cell D3 appears in row 4. x will be based on the number entered into A1

I have used an offest forumla to get numbers 1 and 3 working but can not get number 2 into my formula.

Any help would be really appreciated.

Cheers

 

[Andrew Poulsom]

Can you post the formula you tried? Also, some sample data would be helpful.

 

[bat18]

Can you post the formula you tried? Also, some sample data would be helpful.

=SUMPRODUCT(($C$5:$C$822=$B1)*OFFSET($AE$5:$AE$822, 0, 0, ROWS('[Unity Database 10.05.12.xlsx]Input'!$AE$5:$AE$822),($A$1)))

This formula is what i had originally which summed from column AE onwards for x amount of columns depending on what was entered into A1.

I need to adapt this now but have just realised there may be an easier way.

I simply need to sumif based on data in a row and not a column.

So for example

Every time the word 'Food' is in row 4 you would sum the cells directly underneath the word food.

That is the first problem

BUT

I only want to sum the first x amount of times 'Food' appears in the row and that is dependent on the number entered into A1.

Let me know if that made any sense. Basically ignore the first post in this thread as this is now my problem.

Cheers

 

[Andrew Poulsom]

Are the Food entries in row 4 contiguous?

 

[bat18]

No but they are equally spaced apart, although i would rather the formula picked it up regardless of where it appeared in row 4.

 

[Andrew Poulsom]

With this sample data in A4:L14:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
4	Food	Other	Other	Food	Other	Other	Food	Other	Other	Food	Other	Other
5	49	7	1	84	8	32	94	39	70	72	7	86
6	20	79	80	36	73	36	82	31	19	6	53	75
7	16	71	53	95	48	86	4	5	65	89	66	61
8	100	26	81	42	90	65	67	72	4	66	22	26
9	31	8	43	80	38	79	31	7	82	6	68	79
10	50	63	4	44	87	24	19	27	44	86	76	69
11	15	99	46	58	91	84	3	1	63	48	45	57
12	88	21	27	5	74	61	94	93	80	16	17	10
13	43	53	78	84	58	25	50	1	74	59	8	11
14	47	13	62	26	21	95	61	38	18	89	37	63
Sheet4

try this formula:

=SUMPRODUCT((A4:L4="Food")*A5:L14*(COUNTIF(OFFSET(A4,0,0,1,COLUMN(A4:L4)),"Food")<=A1))