Sumif and arrays question.

[Jeffrey20]

100	200	300
400	300
650

<tbody>
</tbody>

Hi, I have a problem I wanna sumif the three columns if the sum of the rows (each one) is >=650.

For example

600

700

650

<tbody>
</tbody>

since those are the sums of each row, the formula should give a 1350 value. (should sum A2,A3,B2,B3,C2,C3)

I think these can be done using array formulas but my knowledge with those is minimum. I do realize is easier just to add a fourth column with the row sum, but that is not what I need.


Thanks

 

[Jonmo1]

Try

=SUMPRODUCT((A1:A3+B1:B3+C1:C3>=650)*(A1:C3))

 

[Jeffrey20]

Thanks, that seems to work.

But I also need countif (should be "2" for the example), averageif (should be "675"), min (650) and max(700) for that same situation. (sorry I didnt ask before, but I figure I was gonna get a sumif solution so I would be easy to adapt it to those others).


Hope I'm not much of a bother.

 

[Dave Patton]

Count =SUMPRODUCT(--(A1:A3+B1:B3+C1:C3>=650))

 

[Dave Patton]

Excel Workbook
	A	B	C	D
1	100	200	300	600
2	400	300		700
3	650			650
4
5	>=	650
6	Sum	1350	1350
7	Count	2	2
8	Average	675	675
9	Max	700	700
10	Min	600	650
2a

Excel 2003

Cell Formulas
Range	Formula
D1	=SUM(A1:C1)
D2	=SUM(A2:C2)
D3	=SUM(A3:C3)
B6	=SUMPRODUCT((A1:A3+B1:B3+C1:C3>=B5)*(A1:C3))
B7	=SUMPRODUCT(--(A1:A3+B1:B3+C1:C3>=650))
B8	=B6/B7
B9	=MAX(SUM(A1:C1),SUM(A2:C2),SUM(A3:C3))
B10	=MIN(SUM(A1:C1),SUM(A2:C2),SUM(A3:C3))
C6	=SUMIF(D1:D3,">="&B5)
C7	=COUNTIF(D1:D3,">="&B5)
C8	=C6/C7

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Jeffrey20]

Hi, thank you very much for taking interest, although as I stated in the original post, adding a fourth column with the row sum isn't an option.

I sure there has to be a way of doing what I want the way I want it.

Right now I guess I'll just use a fourth row since this is time sensitive, but I'd still like to know the proper (if it exists) way of doing that, just to gain knowledge.

 

[Dave Patton]

Then use the Functions IF & AND
The other formula gives the maximum which can be excluded and one row is less than 650 so it can be excluded.
The other sum is your required minimum.

 

[Jeffrey20]

The problem is that I can't predefine the rows, I need to make the ranges as whole column, so using that approach won't work. That's the reason I think this can only (if possible) be obtain with array functions. (or user-defined functions, but I don't wanna use them)

 

[barry houdini]

This "array formula" will give you the sum of all rows >=650

=SUM(IF(SUBTOTAL(9,OFFSET(A1:C1,ROW(A:A)-1,0))>=650,SUBTOTAL(9,OFFSET(A1:C1,ROW(A:A)-1,0))))

confirmed with CTRL+SHIFT+ENTER

That applies to the entire column and you can easily extend to more than 3 columns by changing A1:C1.

replace SUM, with AVERAGE, MIN or MAX as required, for a count use this version

=COUNT(IF(SUBTOTAL(9,OFFSET(A1:C1,ROW(A:A)-1,0))>=650,1))

also array entered

 

[Jeffrey20]

Genius! Thank you very much.