Number of Different Values in a Range Based on Criteria

[Mgc26133]

Hello all

I have a range of plate numbers (of cars), and the dates the cars used priced roads. What I need is a formula to give me the number of different cars used by HQ in a given month.

For example in January the car AAA has used priced roads twice, but since it's the same car, the number has to be "1".

I'm going to put this formula to the "value if true" of an IF function, so if the solution can support that, that would be much helpful.

Attached a sample file to better explain and for you to work on.

Thanks in advance.

[bebo021999]

Stay in cell E4
Because months in column A are merged, we have to limit the active range of row 4 within its month "JAN":
Create a User Define name (Ctrl-F3)
Name: Plate
Refer to: =INDEX(INDEX($C$1:$C4,LOOKUP(2,1/($A$1:$A4<>""),ROW($A$1:$A4))):$C4,)
Then in E4:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Drag down.

[Bo_Ry]

Please try at E4

=IF(EOMONTH(B4,0)=EOMONTH(B5,0),"",COUNT(1/FREQUENCY(IF(D$4:D4="hq",IF(EOMONTH(B4,0)=EOMONTH(+B$4:B4,0),MATCH(C$4:C4,C$4:C4,))),ROW(D$4:D4)-ROW(C$3))))

confirm with Ctrl+Shift+Enter

[Mgc26133]

Bebo & Rory

Is it possible to make the formulas once per month, rather than having them dragged down the whole range? Reattaching the sample file for better explaining and visualization.

(I can't use Pivot because the main table is taking values from multiple sheets for a comprehensive report)

[bebo021999]

Column J is 1st day of month, formated as "mmm"
Column K:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Bo_Ry]

Please try at M5

=COUNT(1/FREQUENCY(IF($D$4:$D$999="hq",IF(TEXT($B$4:$B$999,"mmm")=L5,MATCH($C$4:$C$999,$C$4:$C$999,))),ROW($C$4:$C$999)-ROW(C$3)))

confirm with Ctrl+Shift+Enter

[Mgc26133]

Bo_Ry & Bebo

Both your formulas are working! But now the problem is, how can I adapt the formula to an evergrowing range? Because the cars aren't sitting, there are new toll usages every month so the range is growing. I tried to turn the range references into column references to accommodate the grow, like B:B instead of B4:B35. But somehow I got the feeling that excel didn't really appreciate me doing that.

[JeteMc]

Put column headers in cells B3:D3 and convert the range B3:D35 into an Excel table. (Ctrl + t)
The formula for cells M5 and down could be modified to read: =COUNT(1/FREQUENCY(IF($D$4:$D$35="hq",IF(TEXT($B$4:$B$35,"mmm")=L5,MATCH($C$4:$C$35,$C$4:$C$35,))),ROW($C$4:$C$35)-ROW(C$3)))
When cell D35 is selected and the Tab key is pressed then the formula should automatically update to include row 36.
Notice: Remember to reactivate the formula in cell M5 by simultaneously pressing the Ctrl, Shift and Enter keys after making the modification.
Let us know if you have any questions.

[kvsrinivasamurthy]

PL see file.
NOTE : I have included few blank rows 35-40. Formula works for that. If you want to include more rows for future use Change 40 in formula to required value. To change range of data also change 40 to required value. Any clarifications welcome.
ARRAY formula in L5 then copied down.

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[Mgc26133]

Hello all!

I tried your suggestions, but I met the same problem. I can't copy syntax formulas with curly brackets, and when I convert range references to column references, excel started using excessive CPU.

A colleague of mine was able to come up with the following during the weekend:

=SUM(IF(COUNTIFS(B:B,">="&DATE(2021,1,1),B:B,"<="&EOMONTH(DATE(2021,1,1),0);C:C,UNIQUE(C:C,FALSE,FALSE),D:D,"HQ")>0,1,0))

If feels much cleaner and did not force the excel despite the full column references, so I think I'm going to use this.

Thanks for all your time!

[JeteMc]

I am glad that you got a solution, however I would point out that the UNIQUE function is only supported by the Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web Excel for iPad, Excel for iPhone Excel for Android tablets and Excel for Android phones versions of Excel. We were all trying to give you a formula based on functions that are supported by the 2016 version since that is what is given in your profile. Please update your profile to show the version of Excel that you are using to avoid such occurrences in the future.
I hope that you have a blessed day.

[Mgc26133]

Office was installed to this computer in 2016 so that's what I thought to add. I didn't realize formulas differ from version to version, because until now it's never been an issue. Thanks for pointing it out, I just updated the profile.