Countifs formula Error

vstr88889

Hi, I have one main sheet and i'm going to pull data from that to another sheet, in this case i need to count my new Client Details within the particular date range, the problem is Project name details is repeating in the colounm (Single project has multiple products so i've kept seperate entry for all the products due to this project list is repeating) and i'm not sure how to use the date range in the same colounm twice.

1. Need to count the client detail.
2. within the particular date range
3. and date's also repeating.

I've tried countifs, countif with and, or functions still getting the error.

Nick Korna

Hi @vstr88889,

For this you would want something like this (using between 8/4/2024 and 7/8/2024 inclusive):

=COUNT(DISTINCT(COLLECT([Est.Details]:[Est.Details], ERD:ERD, @cell >= (DATE(2024, 8, 4)), ERD:ERD, @cell <= (DATE(2024, 8, 7)), Status:Status, "Production in Progress")))

Example:

You can substitute the date function with a cell or sheet summary reference as required.

Hope this helps or at least gives you some idea of how to proceed, but if you've any problems/questions then just let us know!

vstr88889

Hi, First of all thank you so much for your valuable reply, i've tried the same, formula Works like a charm, but if i try to reference the date cell instead of typing it shows the wrong value, if i manually enter the date it works fine.

Here is the formula i've used with date reference. (Value Wrong)

=COUNT(DISTINCT(COLLECT({Sridhar Mastersheet Range 1}, {Sridhar Mastersheet Range 4}, @cell >= (DATE([Start Date]@row)), {Sridhar Mastersheet Range 4}, @cell <= (DATE([End Date]@row)), {Sridhar Mastersheet Range 3}, "Production in process", {Sridhar Mastersheet Range 2}, "Production")))

Here is the formula i've used with date Manual entry

=COUNT(DISTINCT(COLLECT({Sridhar Mastersheet Range 1}, {Sridhar Mastersheet Range 4}, @cell >= (DATE(2024, 7, 1)), {Sridhar Mastersheet Range 4}, @cell <= (DATE(2024, 7, 3)), {Sridhar Mastersheet Range 3}, "Production in process", {Sridhar Mastersheet Range 2}, "Production")))

Thanks & regards

Vinoth

Nick Korna

If you're using a cell reference (i.e. a cell with the date type for the column), you don't need the @ cell in the formula - just the cell reference. Using the example data, with an extra column for a couple of dates:

=COUNT(DISTINCT(COLLECT([Est.Details]:[Est.Details], ERD:ERD, >=[Date selection]1, ERD:ERD, <=[Date selection]2, Status:Status, "Production in Progress")))

Similarly if you want to put your dates in the sheet summary instead of having dates on the sheet itself:

=COUNT(DISTINCT(COLLECT([Est.Details]:[Est.Details], ERD:ERD, >=[Date from]#, ERD:ERD, <=[Date to]#, Status:Status, "Production in Progress")))

Hope this fixes the issue for you, but let us know if you've any further issues. 🙂

vstr88889

Hi NIck,

As i attached above, if i use the reference range in the same sheet it's working fine at the same time if i use the reference range from the different sheet the value always return as 0 or 1.

Thanks in advance.,

regards

Vinoth

Nick Korna

In your actual data, how many results are you expecting?

You look to have added an extra status (Range 2, looking for "Production"), but this shouldn't stop thing working unless the criteria aren't met.

Using the previous data to do a cross-sheet example:

It should work OK.

If you check your data for expected results and let us know what isn't working (use a filter on the data sheet to see).

The other potential failing may be if any of the ranges contains a formula and there are cells with errors in, in which case these would need correcting (e.g. use an IFERROR giving a blank result).

vstr88889

I'm not sure what causing the issue, i'm expecting the value - 8, but in return i always get 0 or 1..

Nick Korna

The ERD column in your screenshot looks very blank (which is the date, I think?) - is this potentially the cause of the problem? The formula is looking for rows which have a date between 2 values and non are available to check.

vstr88889

the date column type is by default text/number that's the error., now it's working fine…Thank you so much for the help…