CODEWITHSUNDEEP
excel
Cr1ms0nStraY
Cr1ms0nStraY

Reputation: 81

Using countifs in excel

I have some problem using this function.The thing is whatever I do, it only counts 0. So I got 3 different sheets in excel with different information in it for random people.What I need to do is, compare different columns with information which had some kind of criteria in it.

For example:

In sheet1 there is column for their salary.

In sheet2 there is the max. salary you can receive in the current company, and Yes/No column if they are paying taxes or not.

In sheet3 there are 2 columns with what fee everyone pays and what loans they have.

So what I need to do is in sheet4 to count how many of them receive salary $50 less or more than the max. one, pay loan $80 greater than their fee, and are paying taxes.

I tried different things with countif and countifs, but it always shows me 0.Thanks for your time.

Upvotes: 0

Views: 238

Answers (3)

pnuts
pnuts

Reputation: 59440

I think a formula such as: 

=COUNTIFS(G:G,">"&80,D:D,1,C:C,">"&max_sal+50)+COUNTIFS(G:G,">"&80,D:D,1,C:C,"<"&max_sal-50)

might work with a helper column in Sheet3 for the difference between loan and fee, the tax flag 1 (pays) and 0 (does not pay) and a PivotTable in Sheet4 constructed from multiple consolidation ranges with column headings for Salary, Tax, Fee, Loan and diff (in that order) starting in C4 - but it is something I am not keen to test without sample data.

And, like others, I would take a helper column approach.

Upvotes: 0

WGS
WGS

Reputation: 14169

NOT FOR POINTS. I personally don't consider this the best possible answer anyway.

There can be an elegant solution somewhere or that someone else can present. However, I'd propose that you take the long route on this one and set up helper columns.

You can do three individual checks and one final one:

  1. For the comparison between the max salary and the employee's current salary, check if the ABS difference is within 50.
  2. Tax status is an easy IF or equality check.
  3. For the loans, just do a simple check if: (LOAN-FEE) >= 80.
  4. Multiply the truth value of all three above.

If your employee names are in every sheet, then reconstructing the table in Sheet4 is a simple matter of VLOOKUP.

Assuming a reconstructed table, here's my attempt:

enter image description here

Open the image in another tab to see it clearly.

The formulas for G2, H2, I2, and J2, are the following:

G2: =ABS(C2-B2)<=50
H2: =D2="Y"
I2: =(F2-E2)>=80
J2: =G2*H2*I2

Obviously, the sum of the values in the J column is your count. Or you can just count how many 1 there are in the column. The approach is basically up to you.

The advantage to this is you can see right away who is qualified for which check. A simple filter on any of the Check columns can isolate specific employees. In a sense, the accuracy afforded by having helper columns is much more than what a formula can give you.

Feel free to download my sample worksheet here.

Upvotes: 1

Nathaniel Payne
Nathaniel Payne

Reputation: 2827

Option 1 - Advanced Filters

In order to do this, you might look at a useful alternative option .... advanced filters. Advanced filters do require you to aggregate your data together on one page, but then give you the power to query over multiple criteria. Here is a link to the advanced filter section for Microsoft Excel 2010. It would be a great place to start if you want to move outside of just using basic formulas.

If you do go down this route, then follow the directions on the site in terms of steps:

  • Insert the various criteria that you have selected in the top rows in your spreadsheet and specify those rows in the list range

    • Set the criteria range to the place holding all your data on a single worksheet

    • Run the filter and look at the resulting data. You can easily do a count on the number of records in that reduced data set.

enter image description here

Option 2 - Pivot Tables

Another option that you might look at here would be to use Pivot tables, although again, your data would have to be aggregated together on one worksheet. Pivot tables and pivot charts are just phenomenal tools that I use in the workplace every day to accomplish exactly what you are looking for.

Option 3 - Using Visual Basic

If you were really adventurous, you could choose to try using visual basic code to write a solution. This would give you perfect control as you could specify exactly the ranges to look at for each of the conditions. Unfortunately, you would need to understand VB code in order to use this solution. There are some excellent online resources available that can help with this.

Upvotes: 1

Related Questions