Nested IF with AND and OR.. Is it possible?

[trosasco]

I'm working on a report where I have to track all Active files in Illinois only. The problem is that there are a lot of different types of "Active" files - All these files are considered "Active":

Column F = Active
Column G = Pending
Column K = Welcome Call
Column L = 90-Day Savings Hold
Column M = Temp-Active
Column P = Active-No Funds
Column Q = Pending-No Funds
Column S = IL Active

The states are all together under Column E and the statuses are all together under Column C, but to separate them, I created columns for each of them with their own totals going across. How do I create a formula that will count all these columns but only for the state of IL?

[Harribone]

Not 100% sure what you're requirement is. If you are trying to add values from a column (say A) from each row when the state in col E is "Illinois" and col C is "Active" then you can use:

=SUMIFS(A:A,E:E,"Illinois",C:C,"Active)

If you want to count the number of rows with the the same criteria then you can use:

=COUNTIFS(E:E,"Illinois",C:C,"Active)

If this is not along the lines of what you require can you please upload a workbook with mock data (same layout etc as your actual file) and include you expected result(s) typed in manually so we can check that any solution offered matches your expected outcome.

[Sun13Banjo]

Assuming you record a "Yes" value in each of these columns to indicate whether an account is active, and you want to count the number of "Yes" entries, try
=IF(E1="Il",COUNTIF(F1:S1,"Yes"),"")

[trosasco]

Thank you anyway.. I figured it out.