Forum Discussion
Using and IF/And to add to an array formula to count
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
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)))}
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
- 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))- 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
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.
