Excel 2010 - Count Unique values only in a SUBTOTAL cell

MrsAdmin picture MrsAdmin · Jan 2, 2014 · Viewed 17.1k times · Source

Info: Excel 2010 (no macros)
Data Origns: MySQL Query / phpMyAdmin Server: Apache
Code is run via: Server SQL Query (copy & paste in the phpMyAdmin) or in MySQL Workbench or using a custom shopping cart manager.
Exports to: Excel (.csv then to .xlsx for sales reports)
Notes: The workbook uses my Query as well as other data

Question
I have 2 formulas. 1 that works, 1 that is close but ultimately wrong.
1) Gets the total of Unique OrderIDs (in my case 2342 of them) however it won't reduce to a 'SUBTOTAL' when I filter my data.
=SUMPRODUCT((A8:A30001<>"")/COUNTIF(A8:A30001,A8:A30001&""))

=SUM(IF(FREQUENCY(IF(LEN(A8:A10001)>0,MATCH(A8:A10001,A8:A10001,0),""), IF(LEN(A8:A10001)>0,MATCH(A8:A10001,A8:A10001,0),""))>0,1)) (SHIFT+CTRL+ENTER)

2) Returns as SUBTOTAL however the count is out by 1 (Returns 8, not 7):
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),,1)), IF(A7:A10000<>"",MATCH("~"&A7:A10000,A7:A10000&"",0))),ROW(A7:A10000)-ROW(A7)+1),1)) (SHIFT+CTRL+ENTER)

=SUM(--(FREQUENCY(IF(SUBTOTAL(3, OFFSET(A7:A10000, MATCH(ROW(A7:A10000), ROW(A7:A10000))-1, 0, 1)), COUNTIF(A7:A10000, "<"&A7:A10000), ""), COUNTIF(A7:A10000, "<"&A7:A10000))>0)) (SHIFT+CTRL+ENTER)

Data
This has been filtered: Month of Order, Products ID = 7 Units

OrdID   ProdName    Qty
2525    test            1
2538    test            1
2553    test            1
2555    test            1
2556    test            1
2564    test            1
2566    test            1 

Thanks in advance for your insight.

Answer

barry houdini picture barry houdini · Jan 2, 2014

I would expect formula in 2) to work OK, are you sure the discrepancy in results isn't caused by different ranges - your first formula starts at row 8 and the second at row 7? Are you including the header row in the data hence 8 as the result rather than 7?

If order Ids in column A are numeric then this version should also work

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),,1)),A7:A10000),A7:A10000),1))