sum(if...) help needed!
 

Hello,
Some time ago I created formula using sum & if to sum up values if
previous 3 colums are specific text.

For example: if A colums is xx and B column has yy and C column has zz
then sum up values from column D specific rows in which A is xx, B is yy
and C is zz (A, B and C columns are text values).

I came up with such formula:
=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('Sh eet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12: 'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$443 ))

Now the trick is that straight after enetring this formula it does not
work. I had to click something (some function in Excel) which caused
this formula to work and after I did click it the formula changed and
looks like this:
{=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('S heet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12 :'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$44 3))}
added brackets at beginning and end.

Problem is that I have to add some values to it and copying the formula
to other cell does not make it work. I still have to click something to
make it work, but I forgot what it was and cannot work it out!
Does anybody maybe knows what it is and what I have to click???

Please help! I am desperate as this is HUGE file with a lot of data and
variables in it !!!

Thank you in advance.

sum(if...) help needed!
 

The formula with the braces {} around it is an array formula. You enter it
by pressing Ctrl+Shift+Enter, not just Enter. Do not type in the {}.
Excel adds those to indicate that the formula is an array formula

Tyro

"Jarek" wrote in message
...
Hello,
Some time ago I created formula using sum & if to sum up values if
previous 3 colums are specific text.

For example: if A colums is xx and B column has yy and C column has zz
then sum up values from column D specific rows in which A is xx, B is yy
and C is zz (A, B and C columns are text values).

I came up with such formula:
=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('Sh eet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12: 'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$443 ))

Now the trick is that straight after enetring this formula it does not
work. I had to click something (some function in Excel) which caused this
formula to work and after I did click it the formula changed and looks
like this:
{=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('S heet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12 :'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$44 3))}
added brackets at beginning and end.

Problem is that I have to add some values to it and copying the formula to
other cell does not make it work. I still have to click something to make
it work, but I forgot what it was and cannot work it out!
Does anybody maybe knows what it is and what I have to click???

Please help! I am desperate as this is HUGE file with a lot of data and
variables in it !!!

Thank you in advance.

sum(if...) help needed!
 

THANK YOU ! ! !
I new it was someting simple ...
You saved me a lot of trouble!
Thank you once again!

Tyro pisze:
The formula with the braces {} around it is an array formula. You enter it
by pressing Ctrl+Shift+Enter, not just Enter. Do not type in the {}.
Excel adds those to indicate that the formula is an array formula

Tyro

"Jarek" wrote in message
...
Hello,
Some time ago I created formula using sum & if to sum up values if
previous 3 colums are specific text.

For example: if A colums is xx and B column has yy and C column has zz
then sum up values from column D specific rows in which A is xx, B is yy
and C is zz (A, B and C columns are text values).

I came up with such formula:
=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('Sh eet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12: 'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$443 ))

Now the trick is that straight after enetring this formula it does not
work. I had to click something (some function in Excel) which caused this
formula to work and after I did click it the formula changed and looks
like this:
{=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('S heet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12 :'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$44 3))}
added brackets at beginning and end.

Problem is that I have to add some values to it and copying the formula to
other cell does not make it work. I still have to click something to make
it work, but I forgot what it was and cannot work it out!
Does anybody maybe knows what it is and what I have to click???

Please help! I am desperate as this is HUGE file with a lot of data and
variables in it !!!

Thank you in advance.

sum(if...) help needed!
 

You're welcome

Tyro

"Jarek" wrote in message
...
THANK YOU ! ! !
I new it was someting simple ...
You saved me a lot of trouble!
Thank you once again!

Tyro pisze:
The formula with the braces {} around it is an array formula. You enter
it by pressing Ctrl+Shift+Enter, not just Enter. Do not type in the {}.
Excel adds those to indicate that the formula is an array formula

Tyro

"Jarek" wrote in message
...
Hello,
Some time ago I created formula using sum & if to sum up values if
previous 3 colums are specific text.

For example: if A colums is xx and B column has yy and C column has zz
then sum up values from column D specific rows in which A is xx, B is yy
and C is zz (A, B and C columns are text values).

I came up with such formula:
=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('Sh eet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12: 'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$443 ))

Now the trick is that straight after enetring this formula it does not
work. I had to click something (some function in Excel) which caused
this formula to work and after I did click it the formula changed and
looks like this:
{=SUM(IF(('Sheet1'!$C$12:'Sheet1'!$C$443="xx")*('S heet1'!$D$12:'Sheet1'!$D$443="yy")*('Sheet1'!$B$12 :'Sheet1'!$B$443="zz");'Sheet1'!E$12:'Sheet1'!Q$44 3))}
added brackets at beginning and end.

Problem is that I have to add some values to it and copying the formula
to other cell does not make it work. I still have to click something to
make it work, but I forgot what it was and cannot work it out!
Does anybody maybe knows what it is and what I have to click???

Please help! I am desperate as this is HUGE file with a lot of data and
variables in it !!!

Thank you in advance.