COUNTIF - across 2 columns

R

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
 
G

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
 
R

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
 
J

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
 
E

Eduardo

Hi,
try

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

change range to fit your needs
 
R

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top