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

[anncshaw]

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.

There are various sites so maybe every time the site name changes then count the number of sites?

=sum(1/(countif(a3069:a6059,a3069:a60529)))}

Many thanks

Ann

[#2]

Hi @anncshaw,

You provided:

Hi use the following formula to sum Purchase Orders in my file.   

Does the sum involve the number of how many Purchase Orders you have, or the sum of the Purchase Order amounts?

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.

How many locations? and what are they? Cities? States? Regions?

There are various sites so maybe every time the site name changes then count the number of sites?

Are these sites locations? or websites?

=sum(1/(countif(a3069:a6059,a3069:a60529)))}

In the sum function you are using, what does the 1 represent?

In the countif function you are using, what is the criterion/criteria?

Return value 
A number representing cells counted.
Syntax 
=COUNTIF (range, criteria)
Arguments 
range - The range of cells to count.
criteria - The criteria that controls which cells should be counted.

example: =COUNTIF(a3069:a6059,"New York")

Your narrative is somewhat ambiguous and cryptic.  Can you please elaborate? or maybe share an anonymized (no private info) version of your workbook, because that would be helpful to us and be able to help you with a solution.

cheers

[#3]

Hi@Yea_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)))}

34	2016 EES PO's
357	2017 EES PO's
512	2018 EES PO's
958	2019 EES PO's
1392	2020 EES PO's
664	2021 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

[#4]

@anncshaw 

You may check

Excel formula: Count unique values with criteria | Exceljet

Excel formula: Count unique text values with criteria | Exceljet

depends on which Excel version do you have.

[#5]

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))

[#6]

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

[#7]

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

[#8]

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.