Countifs(nonduplicate,=,=,=)

[Cflaursen]

Hi All

I need help to do a countifs in column C based of 4 criteria.

1. non duplicate values (sheet 1 - column C)
2. within give month (Sheet 1 - Column H = Sheet 2 - B2)
3. a specific catagory (sheet 1 Column I = sheet 3 - A2)
4. a Specific area (sheet 1 - Column J = sheet 2 C2)


Formula is to be copied vertically.


I hope someone can help me out?

Regards CFL

 

[T. Valko]

Hi All

I need help to do a countifs in column C based of 4 criteria.

1. non duplicate values (sheet 1 - column C)
2. within give month (Sheet 1 - Column H = Sheet 2 - B2)
3. a specific catagory (sheet 1 Column I = sheet 3 - A2)
4. a Specific area (sheet 1 - Column J = sheet 2 C2)


Formula is to be copied vertically.


I hope someone can help me out?

Regards CFL

In other words, you want to count the unique entries in sheet 1 - column C that meet all those other conditions.

What about:

2. within give month (Sheet 1 - Column H = Sheet 2 - B2)

Is Sheet 1 - Column H dates or the month names as TEXT entries? What's in Sheet 2 - B2? A month name as a TEXT entry?

 

[Cflaursen]

Hi T.Valko

You are absolutely right about "In other words, you want to count the unique entries in sheet 1 - column C that meet all those other conditions.
".

Following the answer to your questions:

Is Sheet 1 - Column H dates or the month names as TEXT entries?
These are based om month no and year using the following formula (TEXT(DATE(G2;F2;1);"mmm-åå")).


What's in Sheet 2 - B2? A month name as a TEXT entry?
This is a date in date format (mmm.yy)

Regards CFL

 

[barry houdini]

Try this formula

=SUMPRODUCT((Sheet1!I$2:I$100=A2)*(Sheet1!H$2:H$100=TEXT(B2;"mmm-åå"))*(Sheet1!J$2:J$100=C2)*(Sheet1!C$2:C$100<>"")/COUNTIFS(Sheet1!H$2:H$100;Sheet1!H$2:H$100&"";Sheet1!I$2:I$100;Sheet1!I$2:I$100&"";Sheet1!J$2:J$100;Sheet1!J$2:J$100&"";Sheet1!C$2:C$100;Sheet1!C$2:C$100&""))

extend ranges as required

I'm assuming that this formula in in sheet 2 with the criteria data (you had one criterion listed as sheet 3)

 

[T. Valko]

Hi T.Valko

You are absolutely right about "In other words, you want to count the unique entries in sheet 1 - column C that meet all those other conditions.
".

Following the answer to your questions:

Is Sheet 1 - Column H dates or the month names as TEXT entries?
These are based om month no and year using the following formula (TEXT(DATE(G2;F2;1);"mmm-åå")).


What's in Sheet 2 - B2? A month name as a TEXT entry?
This is a date in date format (mmm.yy)

Regards CFL

Ok...

I'm in the USA using U.S. English regional date settings. I'm not familiar with this TEXT function format: "mmm-åå".

It looks like it's supposed to be monthmonthmonth-yearyear. But then, you also show another date format of mmm.yy which is pretty much self-explanatory.

So, what exactly does "mmm-åå" mean?

 

[barry houdini]

Hello Biff,

My assumption was that TEXT(B2;"mmm-åå") is the Danish(?) version of TEXT(B2,"mmm-yy") as you supposed......could be wrong, though

 

[Cflaursen]

Hi all

I couldn't get it working so I will provide a better description providing the real layout (sorry for missing to appreciate you proposal barry houdini and others).

3 Sheets are in play:

Sheet 1:
In sheet 1 only relevant cell is $I$2 (containing a Catagory - "AM")
- So for the formula $I$2 is to be met.

Sheet 2:
Sheet 2 is the sheet where the formula has to be (in column I). In sheet 2 also column B and C are relevant as they contain criterias to meet.
- So for the formula in I2 cells B2 and C2 are to be met.

Column B contains month data (date format: mmm-yy)
Column C contains Area data "IIsys" (text format))

Sheet 3:
Is where the count has to take place

The formula needs to count unique instances in Column C that meets the criteria Area, Month and Catagory

Column H contains month data (derived via following formula: TEXT(DATE(G2;F2;1);"mmm-yy")

Column I contains Catagori data ("AM")
Column J contains Area data ("IISys")


Example: Sheet 3 cell C3 is to be counted if unique, ="AM", =defined month, ="IIsys"


P.s.: The formula has to calculate up to 20000 lines so it needs to be effective.


Look forward to brilliant proposals.

Regards CFL

 

[T. Valko]

Hi all

I couldn't get it working so I will provide a better description providing the real layout (sorry for missing to appreciate you proposal barry houdini and others).

3 Sheets are in play:

Sheet 1:
In sheet 1 only relevant cell is $I$2 (containing a Catagory - "AM")
- So for the formula $I$2 is to be met.

Sheet 2:
Sheet 2 is the sheet where the formula has to be (in column I). In sheet 2 also column B and C are relevant as they contain criterias to meet.
- So for the formula in I2 cells B2 and C2 are to be met.

Column B contains month data (date format: mmm-yy)
Column C contains Area data "IIsys" (text format))

Sheet 3:
Is where the count has to take place

The formula needs to count unique instances in Column C that meets the criteria Area, Month and Catagory

Column H contains month data (derived via following formula: TEXT(DATE(G2;F2;1);"mmm-yy")

Column I contains Catagori data ("AM")
Column J contains Area data ("IISys")


Example: Sheet 3 cell C3 is to be counted if unique, ="AM", =defined month, ="IIsys"


P.s.: The formula has to calculate up to 20000 lines so it needs to be effective.


Look forward to brilliant proposals.

Regards CFL

Sorry, I can't make heads or tails of the layout.

 

[Cflaursen]

Can I somehow upload a screenshot here?

 

[Peter_SSs]

Can I somehow upload a screenshot here?

My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.