Need Help Excel Formula

[woodchuck]

Ma'am/Sir

I need help in here:

Supposed i have this data

Cell subs A B C D E F G H
1 20 200 20 2 3 1 3 1 4
2 24 200 30 2 3 1 3 1 4
3 30 100 50 2 3 1 3 1 4
and i need to meet this conditions

X = sum of A/( sum of B- sum of C-(sum of D- sum of E)- sum of F) of cells with subs >= 20

Y = sumA/(sumB-sumC-(sumD-sumE)-sumF) of cell with subs >= 20 and B >= 50


problem is i have thousands of cells to sum up

thanks

 

[Aladin Akyurek]

Ma'am/Sir

I need help in here:

Supposed i have this data

Cell subs A B C D E F G H
1 20 200 20 2 3 1 3 1 4
2 24 200 30 2 3 1 3 1 4
3 30 100 50 2 3 1 3 1 4
and i need to meet this conditions

X = sum of A/( sum of B- sum of C-(sum of D- sum of E)- sum of F) of cells with subs >= 20

Y = sumA/(sumB-sumC-(sumD-sumE)-sumF) of cell with subs >= 20 and B >= 50


problem is i have thousands of cells to sum up

thanks

X:

=SUM(C2:C4)/(SUM(D2:D4)-SUM(G2:G4)-SUMIF(B2:B4,">=20",H2:H4))

is this what you are after?

 

[jasonb75]

Maybe

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	subs	A	B	C	D	E	F	G	H
2	20	200	20	2	3	1	3	1	4
3	24	200	30	2	3	1	3	1	4
4	30	100	50	2	3	1	3	1	4
5
6	Sum X	26.40585
7	Sum Y	2.325581
Sheet

 

[woodchuck]

Maybe

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	subs	A	B	C	D	E	F	G	H
2	20	200	20	2	3	1	3	1	4
3	24	200	30	2	3	1	3	1	4
4	30	100	50	2	3	1	3	1	4
5	*	*	*	*	*	*	*	*	*
6	Sum X	26.40585	*	*	*	*	*	*	*
7	Sum Y	2.325581	*	*	*	*	*	*	*
Sheet

tried this but i got #VALUE!

 

[jasonb75]

Did you try Aladin's suggestion?

#VALUE! means you didn't enter the formula correctly.

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

 

[woodchuck]

Did you try Aladin's suggestion?

#VALUE! means you didn't enter the formula correctly.

havent tried his suggestion,

heres my sample trial of your suggestion still i get #VALUE!

 

[woodchuck]

X:

=SUM(C2:C4)/(SUM(D2:D4)-SUM(G2:G4)-SUMIF(B2:B4,">=20",H2:H4))

is this what you are after?

not quite sir, i think sir jayson's suggestion is correct but cant seem to make it work i got #VALUE!

thanks

 

[jasonb75]

With the cursor in the formula, so it shows the ranges coloured as it does in your screendump, hold Shift + Ctrl and press Enter.

 

[Desu Nota from Columbus]

Are you sure you used CTRL+SHIFT+ENTER to input the formula?

Your picture doesn't show the brackets around the formula.

 

[Aladin Akyurek]

not quite sir, i think sir jayson's suggestion is correct but cant seem to make it work i got #VALUE!

thanks

Would you specify what the desired values (hand-calculated) are for the sample in your original sample