Need help in creating a COUNTIF formula based on 2 conditions

[Manish_Gupta]

Hello Guys,

I need help in creating a Formula.

There are 2 types of condition given in attached file. Condition 1 is the actual scenario in which any category is having a status as “Important” then it has been marked as 1 in Column C (Yes) and if any category is having a status as “Not Important” then it has been marked as 1 in Column D (No). In cell C11 and D11 “COUNTIFS” formula has been used to get the Total No.

In Condition 2 which is current scenario and for which I need help to create a formula is, if incase Category status is “Important” but Column D (No) is marked as 1 instead of Column C (Yes) then Total of Column D should be get 0 (or show Error) automatically and if incase Category which is “Not Important” has been marked as 1 in Column C (Yes) instead of Column D (No) then Total of Column C should be get 0 automatically.

Means can we create a "COUNTIFS" formula which check if status is "Important" and Column C is marked as 1 then generate the Count of total 1 for Column C but if status is "Important and Column D is marked as 1 then Column C total should be get 0 automatically and same has to be done in case of "Not Important" status also.

Note: The cells has been highlighted in Yellow which needs to be consider for Condition 2 scenario.

Thanks,
MG

[kvsrinivasamurthy]

Pl see attached file with formula.

[icestationzbra]

since you are on XL2007, you can try this formula in D24:

Please Login or Register  to view this content.