Massive Table Need CountIf for Multiple Columns if values are present

Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Good evening gentlemen and women, I'm starting to learn the basics with VLookup, XLookup, Power Query and CountIf Formulas, however I can't seem to wrap my head around how to develop a solution to this one. Basically, I'm running a report on many different companies (X,Y,Z) all in one worksheet. The information contains every single item on their property book, and the main columns I'm looking at is how many the Company has that needs a service (Overdue, or Open), how many require Usage (there's a material number value inputed next to each piece of equipment in a column named Usage), then calibration..so on and so forth. So what I'm thinking is I need a CountIf formula that basically does this; for Usage (Column D) ; CountIFS ("search column A", "Company Name", "search column D", "any material number"). However I keep getting 0 even though I know there are values there. I tried using the "<>" for "any material number", and I got a count of everything that was in Column A as well not just D. So how can I just get a count of column D, if the criteria is the company in column A? I feel like if I figure that out, I can just replicate the formula to match my other columns. I'll have to make a new excel to show an example because unfortunately the one i'm working on is FOUO.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Attached is an example excel thats really simplified. Imagine that I have 13 companies with thousands of equipment and I need to report monthly status reports. It's crazy.
 

Attachments

  • Screen Shot 2020-11-24 at 10.24.00 PM.png
    Screen Shot 2020-11-24 at 10.24.00 PM.png
    118.3 KB · Views: 8

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you considered using a PivotTable ??
 
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Have you considered using a PivotTable ??
Yea, I like Pivot Tables, but when I try to create one using the equipment information it doesn't look good. It's still clustered. I'll need to get the formula to work, create another table with just the numbers I'm trying to get, and build a pivot table off of that one.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,080
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top