Countif across multiple different excel files

Question

overall I need to count how often 3 different suppliers occured in our daily excel files from last year. Every daily file shows around 300 names in column F. For each file I would do a simple countif-function like this:

=countif(F:F;"Supplier1")

Is it possible to extend this to all other daily files if I save them in one specific folder or sth?

COUNTIF can be applied to a single sheet only. So, es, you could combine all your data into one sheet and count only once. However, if you need to count on several or many sheets the result of each count can be added to a total. To utilize that facility you would build a loop, open each worksheet/workbook in turn, and then execute your count with each result being accumulated in a total. — Variatus
– Variatus, Commented Mar 9, 2021 at 23:56
It's possible. The answer could be to import all the data from Workbooks (Excel files) to column F in a single sheet and then use the COUNTIF function for that data set. If you want me to code it let me know. — Gass
– Gass, Commented Mar 10, 2021 at 7:34
oh this sounds great. Because this is a new task not only for past year, I will also have to do it for the current year at least once a week. — Marie
– Marie, Commented Mar 10, 2021 at 13:03

E. L. · Accepted Answer · 2021-03-10 11:19:42Z

2

The only simply way you can do it is to use multiple countif functions with a "+" operator.

=countif(F:F;"Supplier1") + countif('Sheet2!F:F;"Supplier1") + ...

For across multiple workbooks

=countif(F:F;"Supplier1") + countif('[Book2]Sheet2!F:F;"Supplier1") + ...

The first countif function will get the result on column F of the current sheet while the second countif function will get the column "F" from the other book.

edited Mar 10, 2021 at 11:19

answered Mar 10, 2021 at 2:37

E. L.

5426 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gass Over a year ago

From my point of view this does not answer the question. @Marie is talking about using the COUNTIF function for different Workbooks not Worksheets..

E. L. Over a year ago

Basically, the range of the countif function can be from a different workbook. Let me revise my example.

Gass Over a year ago

How does this work? Do you need to have the Workbooks opened?

Marie Over a year ago

I have to try this out. Hope I can take some time for it later ot at least tomorrow

Collectives™ on Stack Overflow

Countif across multiple different excel files

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related