Any Excel guru's onboard?

[TheNissanMan]

Last year I set up a spreadsheet and there is one forumal I can't for the life of me remember how I got it to work!

It' works for one of my staff who was here last year but not for the ones who have changed!

The basic formula is:

=SUM(('Cust Details'!H4:H102="Friday 30th July")*('Cust Details'!J4:J102="Fred Blogs"))

But, to get the formual to work there was a keystroke I had to press to surround the beginning and end of the formula with {} brackets so it looks like:

{=SUM(('Cust Details'!H4:H102="Friday 30th July")*('Cust Details'!J4:J102="Martin Cooksley"))}

It isn't as simple as just typing them in as it doesn't work and I can't remember how or remember the name of the {} brackets to try and google it lol...

Can anyone at all lend a helping hand ?

What it should do when it's working is count how many lines in the cust details worksheet have an occurance of Friday 30th July and Fred Blogs on the same line, COUNTIF won't work as your checking for two details in different fields...

 

[Dazzler]

It's an array formula - hit CTRL-SHIFT-ENTER to get the {}'s

So press F2 to edit the formula and instead of hitting enter - use the combination above

COUNTIF does not work with more than 1 variable (it is possible though)
PivotTables are sometimes a viable option.

 

[TheNissanMan]

It's an array formula - hit CTRL-SHIFT-ENTER to get the {}'s

So press F2 to edit the formula and instead of hitting enter - use the combination above

COUNTIF does not work with more than 1 variable (it is possible though)
PivotTables are sometimes a viable option.

Gavin,

you sir are an absolute legend and Beer Tokens put aside for if we ever meet

 

[Dazzler]

No problem Ryan, glad to help.

They can be tricky to get used to but can be useful functions.

Gav