sum certain cells based on critieria

Good morning,

I need some major help with this. I have been trying for months to figure it out.

In Column A, I have 2 different kinds of text, "3.2" and "3.6". There is no patten to the order.

In Column B, there is a number (lets say either a 1 or a 2).

In cells C1 & C2, I have "3.2" and the number 5, respectively. I want it to find 5 "3.2" rows and add the number's from column B for those 5 rows only.

Hi and welcome to the forum!

Which 5 rows? The first such 5?

Regards

Hi, welcome to the forum

Perhaps add a helper to count the 3.2's, and then use sumifS() to add up what you want, based on that?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Would this work?

=SUMPRODUCT((COUNTIF(INDIRECT("A1:A"&ROW(A1:A10)),C1)<=C2)*(A1:A10=C1)*B1:B10)

Alter the column A and B ranges to fit your ranges.

Hope this helps!

Or, assuming your use of quotation marks (e.g. "3.2","3.6") was not indicative of the fact that those values are text values, array formula**:

=SUM(IF(A1:A10=C1,IF(ROW(A1:A10)<=SMALL(IF(A1:A10=C1,ROW(A1:A10)),5),B1:B10)))

Obviously if your range is not A1:A10 then amend accordingly (though I strongly recommend you keep the end range reference - 10 here - as small as feasibly possible; and certainly don't use entire column references in an array formula).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

2 more options...

	A	B	C	D	E	F	G	H	I
1	Version 1		Helper			Version 2
2	1	3.2	1	24		1	3.2	1	24
3	2	3.2	2			2	3.2	2
4	3	3.6	2			3	3.6
5	4	3.6	2			4	3.6
6	5	3.2	3			5	3.2	3
7	6	3.6	3			6	3.6
8	7	3.2	4			7	3.2	4
9	8	3.6	4			8	3.6
10	9	3.2	5			9	3.2	5
11	10	3.2	6			10	3.2	6

D2=SUMIFS($A$2:$A$11,$B$2:$B$11,3.2,$C$2:$C$11,"<6")
I2=SUMIF(H2:H11,"<6",F2:F11)

This should do the trick:

Please Login or Register  to view this content.

Can you upload a sample workbook that recreates the problem?

See the attached workbook for fix. You had a few conflicting syntax errors with some of your IF statements that was throwing off the sumproduct. The formula is located in cell AD22.

Hope this helps!

I have not looked at the files in the other posts, but I would suggest that change your formula so that the snaswers are values not text...

=IFERROR(IF(MID(K28,12,2)+0=32,"3.2",IF(MID(K28,12,2)+0=36,"3.6","Other")),"")
change to...
=IFERROR(IF(MID(K28,12,2)+0=32,3.2,IF(MID(K28,12,2)+0=36,3.6,"Other")),"")
or maybe even...
=IFERROR(--MID(K28,12,2)/10,"")

Are you willing to add a helper column for your data to ease this process? If you are, check the attached workbook. I have hidden column M which contains the helper formula. Cell AE22 contains the results I believe you are looking for.