COUNTIF - across 2 columns
 

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

Hi Rebekah,
Im sure there is a better way to do this but the below will work.
In column c do: =and(A1=10,B1=2008).
this will produce a true/False result.
Then simply do a countif of on true in column C
--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

Of course, you could always do a count pivot table as well.

--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

I have tried =sumproduct((range = "10")*(range = "2008))

This returned #VALUE

Any other ideas?

Your reply won't work because the formulas are running from another
worksheet, and I the numbers relate to months and years...

B

"Gaffnr" wrote:

Of course, you could always do a count pivot table as well.

--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

This worked for me....
Rob
x

=SUMPRODUCT(($A$1:$A$5=10)*($B$1:$B$5=2008))
--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

=SUMPRODUCT(--(A1:A100=10),--(B1:B100=2008))
Adjust ranges to suit.
--
David Biddulph

"Rebekah" wrote in message
...

I'm hoping somebody can help me, as I feel this formula should be simple,
but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

When you have multiple criteria use SUMPRODUCT()

=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2))

'In your case since it is in a different sheet...with month and year in F1
and F2
=SUMPRODUCT((Sheet2!A1:A10=F1)*(sheet2!B1:B10=F2))

If this post helps click Yes
---------------
Jacob Skaria


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

Hi,
try

=SUMPRODUCT(--($A$8:$A$10=10),--($B$8:$B$10=2008))

change range to fit your needs

"Rebekah" wrote:

I have tried =sumproduct((range = "10")*(range = "2008))

This returned #VALUE

Any other ideas?

Your reply won't work because the formulas are running from another
worksheet, and I the numbers relate to months and years...

B

"Gaffnr" wrote:

Of course, you could always do a count pivot table as well.

--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

=SUMPRODUCT((G1:G100={"letter","tabloid"})*(C1:C10 0*E1:E100)*{1,2})


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

Sorry wrong post.


"Teethless mama" wrote:

=SUMPRODUCT((G1:G100={"letter","tabloid"})*(C1:C10 0*E1:E100)*{1,2})


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

Hi Eduardo

Is it possible that I am getting the #VALUE result because the cells by
which I am running the formula are already products of another formula?

Many thanks

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(--($A$8:$A$10=10),--($B$8:$B$10=2008))

change range to fit your needs

"Rebekah" wrote:

I have tried =sumproduct((range = "10")*(range = "2008))

This returned #VALUE

Any other ideas?

Your reply won't work because the formulas are running from another
worksheet, and I the numbers relate to months and years...

B

"Gaffnr" wrote:

Of course, you could always do a count pivot table as well.

--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10" when
column B records the occurance of "2008".

Please help!

Beks

COUNTIF - across 2 columns
 

I'm surprised that you got a #VALUE! error from that. I would have expected
to get an error reported when you tried to enter the formula, as the quote
marks don't match.
If you didn't actually use that formula, and you've mistyped it into the
newsgroup, I would suggest that in future you don't try to retype. Copy from
the formula bar and paste here.

Secondly, are you sure that you intended to use the quote marks? Are you
looking for 10 and 2008 as text strings or as numbers?
What do =ISTEXT(A2) and =ISNUMBER(A2) show, and similarly for B2?

Thirdly, your formula wouldn't work as you are using range as the name for
both columns, and you asked for the 10 in one column and the 2008 in a
different column. You need two different ranges.
--
David Biddulph

"Rebekah" wrote in message
...
I have tried =sumproduct((range = "10")*(range = "2008))

This returned #VALUE

Any other ideas?

Your reply won't work because the formulas are running from another
worksheet, and I the numbers relate to months and years...

B

"Gaffnr" wrote:

Of course, you could always do a count pivot table as well.

--
Rob Gaffney


"Rebekah" wrote:

I'm hoping somebody can help me, as I feel this formula should be
simple, but
I can't get it to work!

I have 2 columns
A B
10 2008
11 2008
10 2009

I need to count how many time column A records the occurance of "10"
when
column B records the occurance of "2008".

Please help!

Beks