Count and sum values in two columns for each particular value in a third column

Hi. So what I need to do is very simple, but I'm not sure where to start.

I have the following:

County	2010	2011
A1	x
A1	x	x
B2		x
B2	x
B2
C3	x	x
C3		x
D4	x	x
E5		x

I need to be able to calculate how many times each county has a value (i.e., not blank) in 2010, and then again in 2011. In my real spreadsheet, each county might be on there 1 time, 5 times, or 50 times. At the end of the day, I want my formula to be able to calculate that, for example, county A1 has 2 inputs in 2010 and 1 input in 2011; and that B2 has 1 input in 2010 and 1 input in 2011.

Does this make any sense? Thanks for any help.

Hi Askantik, welcome to the forum.

You should be able to use COUNTIF formulas, e.g.

=COUNTIFS(A1:A100,"B2",B1:B100,"x")

That will count every instance where county B2 has an "x" in column B (2010). Change B1:B100 to C1:C100 for 2011 totals.

You could also use a PivotTable, which would make it quick to get your totals.

You could do this with a simple Pivot Table, I think.

Select the range and go to Insert|Pivot Table

Select where you want it to go, click Ok.

Then drag Country down to Row Labels section, drag 2010 and 2011 each to the Summation area.

Hi(Καλησπέρα) and welcome to the forum.

Try this.

=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="x"))

As you have Excel2010, you can do this with COUNTIFS, TOO.

Hope to helps you.