Couple formula questions

  • Thread starter Thread starter Vipo
  • Start date Start date
V

Vipo

I have been thinking my head off with these couple problems and I havent got
it solved. So I have to ask people who are smarter than I am...

Ok, we have this table that has on column A numbers like below and on column
B numbers from 10 to 50.

A B C D D
1 1 10
2 2 20
3 3 30
4 4 40
5 5 50

Now I do a formula SUMIF(a1:a5;">2";b1:b5) and I get 120, which is right
Now do the formula other way =sumif(A1:A5;"D1";B1:B5)
And I have >2 on D1
Excel doesn't understand this (I suppose it tryes to look for text ">2") How
can I make it to use the D1???

Other question is that if I try to "build" a formula from different cells.

A B C D E
1 '=
2 sumif(a1:a5;"
3 >2
4 ";b1:b5)
5

Now I build the formula using next formula: =a1&a2&a3&a4
Now I can see the formula but how can I "activate" it? Only way that I have
thought is to copy it and then I paste it using "values" and after this I
still have to go and press enter on it.
I have to do a big table and if I have to go and press enter key on every
shell my head is going to blow before I get to end...

I hope that you understand what I ment, with my bad english. And if somebody
gets me the answeres I am happy to buy a pint or two :)

Thank you all
Sami
 
hi Vipo
1. Try the following SUMIF formula
=sumif(A1:A5;">" & D1;B1:B5)
and enter 2 in D1
or
=sumif(A1:A5;D1;B1:B5)
an enter '>2' in D1

2. Creating a formula:
for creating cell references use the function INDIRECT. e.g. in C1 you
have entered 'A1:A5' then try the following:
=SUMIF(INDIRECT(C1);D1;B1:B5)
If you want to create the complete formula you have to use VBA (as
INDIRECT only 'transforms' cell references)

Note. I've used the semicolon as separator as your example formula
indicates that you do not use the comma (non english version?).
HTH
Frank
 
First question: leave out the quotes, like in

=SUMIF(A1:A5,D1,B1:B5)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Back
Top