Counting cells using multiple criteria

[robert982]

I have two columns of data. The first 5 cells of Column A contain "Albany, NY". The second 5 cells in column A contain "Atlanta, GA".
Column B contains numbers or blanks...Like this:
Albany, NY 0
Albany, NY 0
Albany, NY 0
Albany, NY (blank)
Albany, NY 5
Atlanta, GA 1
Atlanta, GA 0
Atlanta, GA (blank)
Atlanta, GA 0
Atlanta, GA 1

Column A cells 1-10 are named "Market_Name". Column B cells 1-10 are named "Totals". On another sheet I want to show the number of Albany, NY rows that contain non-blank cells in column B. So, in Boolean terms I want the count of cells where "Market_Name" = Albany, NY AND where "Totals" is not blank. Results should be "4" for either Albany, NY or Atlanta, GA.

Can anyone help with this?

 

[Davefeet]

=IF(B1="A",IF(C1=1,COUNT(B1),"&quot)
Nested IF statement.

Dave

See help under IF function SLC: Olympics 2002, Great skiing, Great Symphony, Great Weather, Great Community, was rated as one of the best places to raise kids.

 

[robert982]

Dave, Thanks! i appreciate the help. I found the following formula would work:
{=SUM(IF(B1:B10<>&quot;&quot;,IF(A1:A10=&quot;Albany, NY&quot;,1,0)))}

 

[AndreasG]

Hello

You can use sumproduct function too

=sumproduct((A1:A10)=&quot;Albany, NY&quot*(B1:B10<>&quot;&quot)

without using array formulas , which are slow and resource consuming on big data.

hth Best Regards
Andreas
using xl2000 on win2k