# Making a formula relative...

#### AndrewKent

##### Well-known Member
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

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))

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

Will SUMPRODUCT do this or simply tally up all three values in the table and add them together?

Hi Andy, have you tried it? SUMPRODUCT can be used for multi-conditional counting; the formula I posted should give an identical result to yours, except you don't need CSE

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Replies
1
Views
608
Replies
8
Views
396
Replies
1
Views
308
Replies
20
Views
1K
Replies
1
Views
104

1,221,497
Messages
6,160,152
Members
451,625
Latest member
sukhman

### 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.

### Which adblocker are you using?

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

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