Forum Discussion

anncshaw's avatar
anncshaw
Copper Contributor
Jun 26, 2021

Using and IF/And to add to an array formula to count

Hi use the following formula to sum Purchase Orders in my file.   I would like to add in and if or if and or some other part so that the formula will also count by location which is in column C. The...
Formulas and Functions
anncshaw's avatar
anncshaw
Copper Contributor
Jun 28, 2021

HiYea_So 

 

The formula is as follows:

 

{=SUM(1/(COUNTIF(A2:A121,A2:A121)))}

This counts the number of POs for year 2016 then in the next row the same formula but different range to count the number of POs for year 2017 etc

{=SUM(1/(COUNTIF(A122:A1233,A122:A1233)))}

{=SUM(1/(COUNTIF(A1234:A3068,A1234:A3068)))}

342016 EES PO's
3572017 EES PO's
5122018 EES PO's
9582019 EES PO's
13922020 EES PO's
6642021 EES PO's

First column contains that formula for the count.

 

The sites refer to Departments and there are two but I need to include them in my formula to include the count if the site is one department and then if it is the other department. These are in column C in my sheet with the following data:

ALEXIS FARMA INT
ALEXIS DUBLIN
ALEXIS FARMA INT
ALEXIS DUBLIN
ALEXIS FARMA INT
ALEXIS DUBLIN

 

So I need the count included for each of these departments.

I hope this makes sense.

 

Many thanks

Ann

 

JMB17's avatar
JMB17
Bronze Contributor
Jun 28, 2021
Try this. Of course, you could use a cell reference for the department.

=SUM((C2:C121="ALEXIS FARMA INT")/COUNTIFS(A2:A121,A2:A121,C2:C121,C2:C121))

  • anncshaw's avatar
    anncshaw
    Copper Contributor
    Jun 28, 2021
    Is it possible to add that countif on site to the following array?

    {=SUM(1/(COUNTIF(A2:A121,A2:A121)))}
    Column C has the sites and there are only two of them.
    Thanks
    Ann
    • JMB17's avatar
      JMB17
      Bronze Contributor
      Jun 28, 2021
      If it's not sensitive information, is it possible to upload a sample of your data (or maybe some dummy data that would be representative of your actual data)?

      Also, could you clarify what version of office you are using? As Sergei noted, if you are using office 365, then there are additional functions available that can be used.
  • anncshaw's avatar
    anncshaw
    Copper Contributor
    Jun 28, 2021
    I am trying to do this but I am getting Value# in the cell.
    Is it possible to add the count if for the site into this array?
    {=SUM(1/(COUNTIF(A2:A121,A2:A121)))}
    Thanks
    Ann

Resources