Making a formula relative...

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Hi folks,

Now I think this is what this particular function is called but I'm not 100% sure. I'm not talking about the "$" function more this example...

Code:
{=SUM(IF('The Log'!$S$2:$S$675=$A$1,IF('The Log'!$B$2:$B$675='Retail Summary'!$E$1,IF('The Log'!$A$2:$A$675=$E$13,IF('The Log'!$J$2:$J$675=E16,1,0),0))))}

...now this formula looks at a table of data and only SUM's each column IF a row meets a certain criteria. Now when I put the formula together it didn't work until I put the "{" and "}" around the whole thing. Then it worked perfectly. I manually checked the table and it has returned the correct values. How and why do I need "{" and "}" for this to work? I'm a complete novice so I have to ask questions!

Many many thanks,

Andy
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Andy

When you say you "put the { and } around the whole thing", I take it you mean you array enter it (ie Ctrl+Shift+Enter)? Physically typing the {} in should not work.

Array-entering means that you are telling Excel that it is using arrays in the formula rather than single scalar values. So, normally:

Code:
=IF('The Log'!$S$2:$S$675=$A$1,...

would only actually take into account one of the values in the S2:S675 range, whereas array-entering tells Excel that it must perform and report on all the values and return the resulting array (ie list of True/False) to the next step in the formula.

Does this help at all? It seems so much clearer when I think it that when I try to explain!

Richard
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Further to Richard's reply.....

you can probably do this more easily with a SUMPRODUCT formula which does not need to be "array entered". Try

=SUMPRODUCT(--('The Log'!$S$2:$S$675=$A$1),--('The Log'!$B$2:$B$675='Retail Summary'!$E$1),--('The Log'!$A$2:$A$675=$E$13),--('The Log'!$J$2:$J$675=E16))
 

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Thanks guys,

Yeah, I should have actually said that its Ctrl+Shift+Enter that achieves this. I take it without this function it would only find the first value to meet this critera whereas the {} round the function would find all that meet the criteria?

SUMPRODUCT: each row in the table is a record and the values in A1, E13 and E16 are the combination that I want to count. So if one record (row) meets these three criterion, it will count it. Will SUMPRODUCT do this or simply tally up all three values in the table and add them together?

Andy
 

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Thanks,

I'm in the middle of testing it out just now, I'll have a look through that link and see what I can find. Thanks again,

Andy
 

Forum statistics

Threads
1,136,268
Messages
5,674,732
Members
419,523
Latest member
Urnovio

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top