I'm trying to count no. values in a column if 2 conditions apply.
Thanks to these discussions I've figured out how to SUM the PRODUCTS
in the column, but now i want to COUNT them!

Example:

No bills/month
Name House Jan-11 Feb-11 Mar-11
A 1 4 3
A 2 2 2
A 3 2
A 1 3 1 2
B 2
C 3 1 4
C 1 6 3
C 2 4 2
C 3 2
C 1 1 3
C 2 3
C 3 2 5 4

and i want to COUNT how many bills/month, per House and per Name....to
create:

Jan-11 Feb-11 Mar-11
A 1 2 1 2
A 2 1 1 0
A 3 1 0 0

Have used this formula, which is close...but no cigar yet, because
when i drag it across the same results come out for each column!

=SUMPRODUCT(($A$3:$A$14="A")*($B$3:$B$14="1"))

Many thanks for your help,

Simon

On Mar 11, 6:22*am, wrote:
I'm trying to count no. values in a column if 2 conditions apply.
Thanks to these discussions I've figured out how to SUM the PRODUCTS
in the column, but now i want to COUNT them!

Example:

* * * * * * * * No bills/month
Name * *House * Jan-11 *Feb-11 *Mar-11
A * * * 1 * * * 4 * * * * * * * 3
A * * * 2 * * * 2 * * * 2
A * * * 3 * * * 2
A * * * 1 * * * 3 * * * 1 * * * 2
B * * * 2
C * * * 3 * * * 1 * * * 4
C * * * 1 * * * 6 * * * * * * * 3
C * * * 2 * * * 4 * * * 2
C * * * 3 * * * 2
C * * * 1 * * * 1 * * * * * * * 3
C * * * 2 * * * 3
C * * * 3 * * * 2 * * * 5 * * * 4

and i want to COUNT how many bills/month, per House and per Name....to
create:

* * * * * * * * Jan-11 *Feb-11 *Mar-11
A * * * 1 * * * 2 * * * 1 * * * 2
A * * * 2 * * * 1 * * * 1 * * * 0
A * * * 3 * * * 1 * * * 0 * * * 0

Have used this formula, which is close...but no cigar yet, because
when i drag it across the same results come out for each column!

=SUMPRODUCT(($A$3:$A$14="A")*($B$3:$B$14="1"))

Many thanks for your help,

Simon

=SUMPRODUCT(($A$3:$A$14="A")*(B$3:B$14="1"))

On Mar 11, 2:20*pm, Donald Guillett wrote:
On Mar 11, 6:22*am, wrote:





I'm trying to count no. values in a column if 2 conditions apply.
Thanks to these discussions I've figured out how to SUM the PRODUCTS
in the column, but now i want to COUNT them!

Example:

* * * * * * * * No bills/month
Name * *House * Jan-11 *Feb-11 *Mar-11
A * * * 1 * * * 4 * * * * * * * 3
A * * * 2 * * * 2 * * * 2
A * * * 3 * * * 2
A * * * 1 * * * 3 * * * 1 * * * 2
B * * * 2
C * * * 3 * * * 1 * * * 4
C * * * 1 * * * 6 * * * * * * * 3
C * * * 2 * * * 4 * * * 2
C * * * 3 * * * 2
C * * * 1 * * * 1 * * * * * * * 3
C * * * 2 * * * 3
C * * * 3 * * * 2 * * * 5 * * * 4

and i want to COUNT how many bills/month, per House and per Name....to
create:

* * * * * * * * Jan-11 *Feb-11 *Mar-11
A * * * 1 * * * 2 * * * 1 * * * 2
A * * * 2 * * * 1 * * * 1 * * * 0
A * * * 3 * * * 1 * * * 0 * * * 0

Have used this formula, which is close...but no cigar yet, because
when i drag it across the same results come out for each column!

=SUMPRODUCT(($A$3:$A$14="A")*($B$3:$B$14="1"))

Many thanks for your help,

Simon

=SUMPRODUCT(($A$3:$A$14="A")*(B$3:B$14="1"))- Hide quoted text -

- Show quoted text -

Thanks, but you've just pasted the formula i've already got....any
insight into what i was trying to achieve, something along the lines
of:

=COUNT(($A$3:$A$14="A")*(B$3:B$14="1"))

Cheers, Simon

wrote in message
...
On Mar 11, 2:20 pm, Donald Guillett wrote:
On Mar 11, 6:22 am, wrote:


[ ]
Have used this formula, which is close...but no cigar yet, because
when i drag it across the same results come out for each column!

=SUMPRODUCT(($A$3:$A$14="A")*($B$3:$B$14="1"))

Many thanks for your help,

Simon

=SUMPRODUCT(($A$3:$A$14="A")*(B$3:B$14="1"))- Hide quoted text -

- Show quoted text -

Thanks, but you've just pasted the formula i've already got....any
insight into what i was trying to achieve, something along the lines
of:

=COUNT(($A$3:$A$14="A")*(B$3:B$14="1"))

Cheers, Simon


Take a close look at those two formulas, paying particular attention to
the [ $ ] in them. Don's formula has a critcal distinction from your
formula.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Mar 11, 4:57*pm, "Clif McIrvin" wrote:
wrote in message

...
On Mar 11, 2:20 pm, Donald Guillett wrote:





On Mar 11, 6:22 am, wrote:

[ ]
Have used this formula, which is close...but no cigar yet, because
when i drag it across the same results come out for each column!

=SUMPRODUCT(($A$3:$A$14="A")*($B$3:$B$14="1"))

Many thanks for your help,

Simon

=SUMPRODUCT(($A$3:$A$14="A")*(B$3:B$14="1"))- Hide quoted text -

- Show quoted text -

Thanks, but you've just pasted the formula i've already got....any
insight into what i was trying to achieve, something along the lines
of:

=COUNT(($A$3:$A$14="A")*(B$3:B$14="1"))

Cheers, Simon

Take a close look at those two formulas, paying particular attention to
the [ $ ] in them. Don's formula has a critcal distinction from your
formula.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)- Hide quoted text -

- Show quoted text -

Thanks for your response Clif, but I did notice that - and having
tried Don's formulae these happen:

a) there's no difference from my example
b) when i drag the formula across to the right it drags the " B$3:B
$14="1")) " with it....ie creating, for example, " F$3:F$14="1"))
"....which i don't want.

Not sure if i explained the context correctly, but i want the 2
conditions to remain the same - ie these bits: (($A$3:$A$14="A")*(B$3:B
$14="1")). They are fine!
What i want to do is COUNT the values in the table when these 2
conditions are true...not SUM, SUMPRODUCT, SUMIF.

Any other suggestions?

Cheers, Simon

The SUMPRODUCT function can be used for both summing and for counting.

You could also use a =SUM(IF(... array formula construction.

If you have XL2007 or later then you could use the COUNTIFS function.

Hope this helps.

Pete

On Mar 15, 5:35*pm, wrote:

Thanks for your response Clif, but I did notice that - and having
tried Don's formulae these happen:

a) there's no difference from my example
b) when i drag the formula across to the right it drags the " B$3:B
$14="1")) " with it....ie creating, for example, " F$3:F$14="1"))
"....which i don't want.

Not sure if i explained the context correctly, but i want the 2
conditions to remain the same - ie these bits: (($A$3:$A$14="A")*(B$3:B
$14="1")). They are fine!
What i want to do is COUNT the values in the table when these 2
conditions are true...not SUM, SUMPRODUCT, SUMIF.

Any other suggestions?

Cheers, Simon