Sum(Sumifs)

[eddiejames]

Hi Everyone,

I'm hoping someone can help me out with a function that isn't working for me.

I want to sum a range (I2:I2500 from 'Sheet 1') based on two criteria; a list of product numbers (range can be found on Sheet 1, K2:K2500) and a date (range found on Sheet 1, N3:N2501) being 'greater than' another date that is populated into a cell (B16).

I have written the following function, but there is an issue with it as it always results in a value of zero:

=SUM(SUMIFS('Sheet 1'!I2:I2500,'Sheet 1'!N3:N2501,">" & B16,'Mould Data'!K2:K2500,{"Product1","Product2","Product3","Product4","Product5","Product6"}))/1000

Can anyone see what I am doing wrong?

I'll give bonus points to anyone who can suggest a way for me to be able to manually update a table with new Product Numbers that can auto populate this function, so that the function doesn't need to be altered if new products are introduced. eg:

=SUM(SUMIFS('Sheet 1'!I2:I2500,'Sheet 1'!N3:N2501,">" & B16,'Mould Data'!K2:K2500,{O7:O12}))/1000

Thank you so much in advance for any help.

Eddie

 

[shift-del]

Hi

Your ranges do not have the same size.

Bonuspoints for Excel tables (CTRL-T) and structured references.

 

[eddiejames]

Hi Shift-del,

Thanks for replying, but I don't think that is the issue.

The ranges all have a size of 2498 cells, except for the 'bonus' one which I just threw in for the sake of illustration. If they weren't the same I believe I would get #VALUE instead of a 0.

Is there anything else that you can see that might be giving me issues?

Eddie

 

[shift-del]

Using my own data I get 0.01 as a result.
So it must be your data. Probably no matches for the criteria. I could as simply as an additional space.

 

[eddiejames]

Thanks for checking again, I will keep persisting.

Eddie

 

[jtakw]

Hi,

This to answer the 2nd part of your question.
Adjust cell references/ranges, add sheet name, accordingly.
Obviously, nothing will work until you fix your data, since at this point, they're Not matching up.

Book3.xlsx
	A	B	C	D	E	F	G	H
1	2	3	a	2	0.046	0.046		a
2	3	5	ba			^using cell referencing for criteria		b
3	4	3	c					c
4	5	4	b
5	6	3	c
6	7	1	b
7	8	5	c
8	9	1	b
9	10	6	c
10	11	9	b
Sheet978

Cell Formulas
Range	Formula
E1	E1	=SUM(SUMIFS(A1:A10,B1:B10,">"&D1,C1:C10,{"a","b","c"}))/1000
F1	F1	=SUMPRODUCT(SUMIFS(A1:A10,B1:B10,">"&D1,C1:C10,H1:H4))/1000

 

[eddiejames]

Thanks so much jtakw! you've collected the bonus points

The rest of it is clearly me not knowing my basics, thanks so much for the assistance guys.

Eddie

 

[jtakw]

Thanks so much jtakw! you've collected the bonus points

The rest of it is clearly me not knowing my basics, thanks so much for the assistance guys.

Eddie

You're welcome, but there're no "bonus points" provision on MrExcel, we Do; however, have the "Like" button