COUNTIF - across 2 columns

[Rebekah]

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

 

[Gaffnr]

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

 

[Gaffnr]

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

 

[Rebekah]

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]

This worked for me....
Rob
x

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

 

[David Biddulph]

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

 

[Jacob Skaria]

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

 

[Eduardo]

Hi,
try

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

change range to fit your needs

 

[Teethless mama]

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

 

[Teethless mama]

Sorry wrong post.

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

 

[Rebekah]

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

 

[David Biddulph]

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.