Check value in a array sum

**johnny_martins00** · 12-14-2011, 05:20 AM

Hi guys,

First Thread!

Im trying to do an array sum, but for each value to sum, i have to check a condition, but im not getting....

Basically it's run the 3 row, and for each value check if the value of the first row, same columm, it's bigger that an predefined value..

s1	s2	s3

100	100	10

Note: i only put 3 rows in example, but in my worksheet there are hundrends...
Can anyone help me on this?

Thanks in advance

Best Regards

**Fotis1991** · 12-14-2011, 05:37 AM

Hi johnny_martins00 and welcome to the Forum

Be sure that the best way to solve your problem, is to attach a workbook.

If i understoond well your point, then try this(S1=100--S2=100---S3=10). iF you want to sum these, if s1>80(per example)

=IF(S1>80;SUM(S1:S3);"")

Hope to helps you.

**daddylonglegs** · 12-14-2011, 06:00 AM

Try SUMIF like this

=SUMIF(A$1:Z$1;">=100";A3:Z3)

That will sum row 3 when the corresponding value in row 1 is >=100

**johnny_martins00** · 12-14-2011, 10:05 AM

testes.xlsx

Hi guys, thanks for your awnsers.

Here goes my attached workbook.

I would like something more dynamic, if possible

.

Let's say i have an array, from D7 until BT7, and i want to sum all the values in that array that for each respective 3rd row, D7->C3 E7->E3 and so on, has a value not bigger than s39.

Is this possible?

thanks
Regards
Joao

**dilipandey** · 12-14-2011, 01:53 PM

Hi Joao,

I am not getting "for each respective 3rd row, D7->C3 E7->E3 and so on, has a value not bigger than s39". Please explain.

Regards,
DILIPandey

< click on below 'star' if this helps >

**johnny_martins00** · 12-15-2011, 05:49 AM

Originally Posted by dilipandey

Hi Joao,

I am not getting "for each respective 3rd row, D7->C3 E7->E3 and so on, has a value not bigger than s39". Please explain.

Regards,
DILIPandey

< click on below 'star' if this helps >

Hi Dili,

In other words, i want to run the 7th row,all values in my range, but for each columm to sum, first i want to check if it's respective 3rd row value it's now bigger that an value.

hope it get it right

Thanks
Best Regards
João Martins

**daddylonglegs** · 12-15-2011, 06:11 AM

To sum row 7 if the value in that row is greater than the respective value in row 3 then you can use SUMPRODUCT like this

=SUMPRODUCT((7:7>$3:$3)+0,7:7)

I'm not sure where S39 comes in - is that supposed to be a cell reference? If so then this version sums row 7 when the above criterion is satisfied but also only if the row 7 value is < the value in cell S39

=SUMPRODUCT((7:7>$3:$3)*(7:7< S39),7:7)

If that doesn't answer your question then please attach a small dummy spreadsheet with representative data - also showing required results, thanks