COUNTIF Formula in Excel with two conditions

[Guest]

Okay I give up! I have tried COUNTIF, SUMPRODUCT, IF, COUNT, and I cannot
get this to work. I have a spreadsheet that if the following two criteria
are met I want to count it.
Column D - "ASA-2"
Column G - "2"

I am counting how many of ASA-2 (customer identifier-column D) entries have
Level 2 issues (column G).

I have looked throught many of the postings and the formulas keep giving me
0 or NAME# error. Yes I did Ctrl-Shift-Enter. Still nothing.

Thanks.

 

[Guest]

SUMPRODUCT is probably the function you would want in this case.

=SUMPRODUCT(--(D110="ASA-2"),--(G1:G10="2"))

Where I foresee that you may run into a problem is with Column G. In your
example you're looking for the text value 2. Is the data in Column G in fact
stored as text? Or is it numbers?

2 and "2" are not the same thing. Remove the quotes from the "2" if your
data is stored as numbers.

HTH,
Elkar

 

[Guest]

Hi,

This is how you can solve the problem using array formulas (Ctrl+Shift+Enter)
The following data is in range D5:E7

ASA-2 2
ASA-3 3
ASA-2 2

In cell E9, array enter the following formula

=SUM(IF(($D$5:$D$7=D5)*(E5:E7=E5),1,0))

Regards,

Ashish Mathur

 

[Guest]

I suggest you to use Conditional Sum Wizard to solve the problem. But it will
work best if you need the result only in one cells. For instance, you have
2-columns and thousand row and you want to count howmany ASA-2 has level 2.

By default, you will not find Conditional Sum Wizard in Tools menu. First
you have to use Add-in from Tools menu to Add Conditional Sum Wizard. After
that it will appear in Tools menu.

Actually you have to sum the Column D (Customer Identifier) and will work
like Count. An example when you will start Conditional Sum:

List Range: sheet1!$d$1:$e$7
Column to Sum: Identity
Column: Is: This Value:
Group = 2 (then second condition)
Identity = ASA-2

Conditional Sum will auto generate a formula for you. But the only problem
is that you can not copy the formula. You have to use CS for every condition.

Hope this information helps you, let us know!