which formula should i use to Calculate How many members?

Hello and welcome to the forum.

One way is with an array formula

Formula:

Please Login or Register  to view this content.

enter this with Ctrl-Shift-Enter

Originally Posted by Indi_Ra

It look like a homework...
In D3 put this formula:

=IF(C3>B3,"exced","under_budget") then drag down.

Hi,

The OP wanted to COUNT the total number of records where actual exceeded budget.

another way, using a normal formula:

=SUMPRODUCT(--($C$3:$C$9>$B$3:$B$9))

....and another, which I must admit I didn't expect to work given the function help only mentions "criteria1 Required. The criteria in the form of a number, expression, cell reference, or text"

Formula:

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

....and another, which I must admit I didn't expect to work given the function help only mentions "criteria1 Required. The criteria in the form of a number, expression, cell reference, or text"

=COUNTIF(C3:C9,">"&B3:B9)

If you get a correct result it's just a coincidence.

The result you get depends on where you enter the formula.

Originally Posted by Tony Valko

If you get a correct result it's just a coincidence.

The result you get depends on where you enter the formula.

Hi Tony,

Funnily enough I'd been thinking about that.

It's not so much where the formula is entered but the fact that it's the B3 cell only in the B3:B9 criteria range that is used as the criteria - which is sort of what I expected and was suprised when it came up with the 'correct' (but co-incidental) result.

Originally Posted by Richard Buttrey

It's not so much where the formula is entered....

It has everything to do with where the formula is entered.

Data Range

	B	C	D	E
1
2			Formula Result	Evaluating As
3	66	36	3	=COUNTIF(C$3:C$9,">"&B3)
4	85	38	1	=COUNTIF(C$3:C$9,">"&B4)
5	39	68	4	=COUNTIF(C$3:C$9,">"&B5)
6	14	59	7	=COUNTIF(C$3:C$9,">"&B6)
7	45	82	4	=COUNTIF(C$3:C$9,">"&B7)
8	87	93	1	=COUNTIF(C$3:C$9,">"&B8)
9	83	20	1	=COUNTIF(C$3:C$9,">"&B9)
10
11			0	=COUNTIF(C$3:C$9,">"&B$3:B$9)
12	------	------	3	=COUNTIF(C$3:C$9,">"&B3)
13

Formula entered in D3 and copied down to D9.

When the formula is entered in D11 it's no longer within an implicit intersection with the data so the result is 0 as expected.

When the formula is entered in D12 as an array formula it returns the count evaluating only the first criteria, >B3.

So... the OP should use EITHER the array at Post 2 OR the non-array at Post 6. Or are they only coincidentally correct, too!!????

Either of those should be just fine.

Whichever floats your boat!

The "coincidentally correct" answers that Excel can throw up are a bit of a pain. I just wanted the OP to be aware that not all that glistens is gold...

Or this

Formula:

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

The "coincidentally correct" answers that Excel can throw up are a bit of a pain.

That's the reason why I have a "thing" about this inaccurate statement:

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

It's possible to get a "coincidentally correct" answer depending on where you enter an array formula that is not CSE'd.

.. and in agreement with you, I amended my canned reply:
Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Originally Posted by Tony Valko

It has everything to do with where the formula is entered.

..but the stuff you show is not what I was referring to. I was simply saying that the array formula I posed self evidently gives the same answer whichever cell it happens to be entered in.

Hmmm...

Originally Posted by Richard Buttrey

....and another, which I must admit I didn't expect to work given the function help only mentions "criteria1 Required. The criteria in the form of a number, expression, cell reference, or text"


=COUNTIF(C3:C9,">"&B3:B9)