Count rows with criteria from two columns?
 

Using Excel 2007 under XP Pro SP3.

I'm new to Excel and spreadsheets in general, so maybe this belongs
under excel.newusers, but a google search didn't show anything that
looked right.

I have two columns of alpha data, each cell can contain "A", "B" or be
blank.

I need formulas to count

1. In how many rows are either or both cells non-blank.
2. In how many rows does either or both cells have the value "A"

--
Bert Hyman St. Paul, MN

Count rows with criteria from two columns?
 

Try these:

1. In how many rows are either or both cells non-blank.

=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))

2. In how many rows does either or both cells have the value "A"

=SUMPRODUCT(--((A1:A10="A")+(B1:B10="A")0))

--
Biff
Microsoft Excel MVP


"Bert Hyman" wrote in message
...
Using Excel 2007 under XP Pro SP3.

I'm new to Excel and spreadsheets in general, so maybe this belongs
under excel.newusers, but a google search didn't show anything that
looked right.

I have two columns of alpha data, each cell can contain "A", "B" or be
blank.

I need formulas to count

1. In how many rows are either or both cells non-blank.
2. In how many rows does either or both cells have the value "A"

--
Bert Hyman St. Paul, MN

Count rows with criteria from two columns?
 

In "T. Valko"
wrote:

Try these:

1. In how many rows are either or both cells non-blank.

=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))

2. In how many rows does either or both cells have the value "A"

=SUMPRODUCT(--((A1:A10="A")+(B1:B10="A")0))


That seems to be the answer.

I'll admit that I'm still somewhat baffled by the SUMPRODUCT function,
but maybe now with some personally meaningful data, I can figure it out.

--
Bert Hyman St. Paul, MN

Count rows with criteria from two columns?
 

Thanks for the feedback!

See this for more on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"Bert Hyman" wrote in message
...
In "T. Valko"
wrote:

Try these:

1. In how many rows are either or both cells non-blank.

=SUMPRODUCT(--((A1:A10<"")+(B1:B10<"")0))

2. In how many rows does either or both cells have the value "A"

=SUMPRODUCT(--((A1:A10="A")+(B1:B10="A")0))


That seems to be the answer.

I'll admit that I'm still somewhat baffled by the SUMPRODUCT function,
but maybe now with some personally meaningful data, I can figure it out.

--
Bert Hyman St. Paul, MN