Forum Discussion
=Count.ifs Multible factors
Hi, I have an issue with =Count.Ifs formal.
I'm using an Excel sheet where all values in columns are based on =VLookup based on factor in coulumn A.
I need to get a summary of the content in several coumns based on twofactors, Part no, and Status.
Approx 20K Rows with data to be "counted" and included in the summary.
Data:
Ferrule 1 (Factor 1) | Ferrule 2 (Factor 1) | Insert 1 (Factor 1) | Insert 2 (Factor 1) | Status (Factor 2) | |
| 1 | 65125-24 | 65125-24 | 6541-32-24 | 6542-24-24 | Prepared |
| 2 | 655962-24 | 655962-24 | 694452-24 | 694462-24 | Produced |
| 3 | 65125-06 | 65125-06 | 6571-121-06 | 6575-122-06 | Produced |
| 4 | 65125-20 | 65125-20 | 6571-35-20 | 6575-35-20 | Produced |
| 5 | 65125-24 | 65125-24 | 6541-32-24 | 6542-24-24 | Prepared |
| 6 | 655962-24 | 655962-24 | 694452-24 | 694462-24 | Produced |
| 7 | 65125-20 | 65125-20 | 6571-35-20 | 6575-35-20 | Produced |
Summary Table:
| Summary: | Prepared | Produced |
| 65125-24 | Qty of 65125-24 with only Factor 2 = Prepared | |
| 655962-24 | ||
| 65125-06 | Qty of 65125-06 with only factor 2 = Produced | |
| 65125-20 | ||
| 6541-32-24 | ||
| 694452-24 | ||
| 6571-121-06 | ||
| 6571-35-20 | ||
| 6542-24-24 | ||
| 694462-24 | ||
| 6575-122-06 | ||
| 6575-35-20 |
You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The layout of the tables in the screenshot is for illustration. You can place the green table to the right of the blue table or on another worksheet.
OliverScheurich Thank you for this, I'm trying to adopt and use the same format, but when the factors and master excel are above 2000 items its not function.
I have to teach myself how to use power query properly.
Thank you!
You are welcome. In order to get started with Power Query i'd recommend this site:
