Sumif and arrays question.

Jeffrey20

Board Regular
 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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try

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

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.

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

Excel Workbook
ABCD
1100200300600
2400300700
3650650
4
5>=650
6Sum13501350
7Count22
8Average675675
9Max700700
10Min600650
2a
Excel 2003
Cell Formulas
RangeFormula
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 {}.

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.

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.

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)

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

Genius! Thank you very much.

Replies
2
Views
305
Replies
3
Views
373
Replies
6
Views
327
Replies
9
Views
339
Replies
21
Views
976

1,203,115
Messages
6,053,590
Members
444,674
Latest member
DWriter9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back