Making a formula relative...

[AndrewKent]

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

{=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

 

[Richard Schollar]

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:

=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]

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]

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

 

[barry houdini]

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

see here for more information on SUMPRODUCT

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

 

[AndrewKent]

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