HELP---- Count Cells in a row based on Month and Unique cell contents.

M

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
370
Office Version
  1. 365
Platform
  1. Windows
Please help, i can get a formula to work on part of what i need but not everything.

I'm need to put a formula in column BJ that firstly looks in Column BJ and looks up month 1 (Jan). (I tried Sumproduct but couldn't make it work). I then need to look in column BI and lookup anything that says "BDM - Nick". Last part is then count how many Unique cells are in BH.
As you can see from my formula's i can get BH and BI to work but can't link in the month calculation. Driving me mad.


2024 Woodford Proactive Calls.xls
BHBIBJBK
12637
1271ONE009BDM - Nick01-Feb9
1281BUI019BDM - Nick01-Jan
1291BUI019BDM - Nick01-Mar37
1301BUI019BDM - Nick01-Jan3
1311BUI019BDM - Nick01-Jan
1321BUI019BDM - Nick01-Jan
1331BUI019BDM - Nick01-Jan
1341ONE009BDM - Nick01-Jan
1351WYC004BDM - Nick01-Jan
1361YES001Mark Harris01-Jan
1371ONE009Mark Harris
1381BUI019Mark Harris
1391BUI019Mark Harris
1401BUI019Mark Harris
1411CS12821Mark Harris
1421CS2922Mark Harris
1431CS11288Mark Harris
1441ONE009Mark Harris
1451BUI019Mark Harris
1461ONE009Mark Harris
1471WRE005Mark Harris
1481CS12821Mark Harris
1491YES001Mark Harris
1501CS2922Mark Harris
1511JAC020Mark Harris
1521JAC020Mark Harris
1531ROO013Mark Harris
1541ROO013Mark Harris
1551BUI019Mark Harris
1561CS12821Mark Harris
1571BUI019Mark Harris
1581PRS002Mark Harris
1591HUW242Mark Harris
1601GAM005Mark Harris
1611MBS003Mark Harris
1621BUI019Mark Harris
1631BUR031Mark Harris
1641WAR037Mark Harris
1651CS11288Mark Harris
1661CS11288Mark Harris
1671ONW001Mark Harris
1681CS15408Mark Harris
1691BUI019Mark Harris
1701BUI019Mark Harris
1711TOP306Mark Harris
1721ONE009Mark Harris
1731BEN3844Mark Harris
1741ONE009Mark Harris
1753JEW562Mark Harris
1761CS12821Mark Harris
1771PIO004Mark Harris
1781BUI019Mark Harris
1791BUI019Mark Harris
1801BUI019Mark Harris
1811ROO013Mark Harris
1821CS2922Mark Harris
1831ALM010Mark Harris
1841BUI019Mark Harris
1851ALM010Mark Harris
1861CS24488Mark Harris
1871CS24488Mark Harris
1881ALL066Mark Harris
1891YES001Mark Harris
1901CS2122Mark Harris
1911BUI019Mark Harris
1921CS24488Mark Harris
1931WAR037Mark Harris
1941CS24488Mark Harris
1951ROO013Mark Harris
1961NCB001Mark Harris
1971ONE009Mark Harris
1981PDT001Mark Harris
1991TRA692Mark Harris
2001BEN3822Mark Harris
2011TRA999Mark Harris
2021WOL164Mark Harris
2031CS12821Mark Harris
2041JAC020Mark Harris
2051CS11288Mark Harris
2061CS15408Mark Harris
2071ROO013Mark Harris
2081CS15408Mark Harris
2091CS15408Mark Harris
2101CS24488Mark Harris
2111CS12821Mark Harris
2121ONE009Mark Harris
2131HUW243Mark Harris
2141CS24488Mark Harris
2151WYC004Mark Harris
2161CS11288Mark Harris
2171CS11288Mark Harris
2181SUR013Mark Harris
2191SUR013Mark Harris
2201WRE005Mark Harris
2211BUI019Mark Harris
2221BEN042Mark Harris
2231LON029Mark Harris
2241CS15408Mark Harris
2251CS12976Mark Harris
Statistics
Cell Formulas
RangeFormula
BK126BK126=SUM(IF(FREQUENCY(IF(LEN(BH127:BH225)>0,MATCH(BH127:BH225,BH127:BH225,0),""),IF(LEN(BH127:BH225)>0,MATCH(BH127:BH225,BH127:BH225,0),""))>0,1))
BK127BK127=COUNTIF(BI127:BI225,"BDM - Nick")
BK129BK129=LET(r,BH127:BH225,ROWS(UNIQUE(FILTER(r,r<>""))))
BK130BK130=LET(r,BH127:BH225,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"BDM - Nick")*(BI127:BI225="BDM - Nick"),NA()))),0))
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hello,

How about

Book1
BHBIBJBK
1271ONE009BDM - Nick1-Feb-243
1281BUI019BDM - Nick1-Jan-24
1291BUI019BDM - Nick1-Mar-24
1301BUI019BDM - Nick1-Jan-24
1311BUI019BDM - Nick1-Jan-24
1321BUI019BDM - Nick1-Jan-24
1331BUI019BDM - Nick1-Jan-24
1341ONE009BDM - Nick1-Jan-24
1351WYC004BDM - Nick1-Jan-24
1361YES001Mark Harris1-Jan-24
1371ONE009Mark Harris
1381BUI019Mark Harris
1391BUI019Mark Harris
1401BUI019Mark Harris
1411CS12821Mark Harris
1421CS2922Mark Harris
1431CS11288Mark Harris
1441ONE009Mark Harris
1451BUI019Mark Harris
1461ONE009Mark Harris
1471WRE005Mark Harris
1481CS12821Mark Harris
1491YES001Mark Harris
1501CS2922Mark Harris
1511JAC020Mark Harris
1521JAC020Mark Harris
1531ROO013Mark Harris
1541ROO013Mark Harris
1551BUI019Mark Harris
1561CS12821Mark Harris
1571BUI019Mark Harris
1581PRS002Mark Harris
1591HUW242Mark Harris
1601GAM005Mark Harris
1611MBS003Mark Harris
1621BUI019Mark Harris
1631BUR031Mark Harris
1641WAR037Mark Harris
1651CS11288Mark Harris
1661CS11288Mark Harris
1671ONW001Mark Harris
1681CS15408Mark Harris
1691BUI019Mark Harris
1701BUI019Mark Harris
1711TOP306Mark Harris
1721ONE009Mark Harris
1731BEN3844Mark Harris
1741ONE009Mark Harris
1753JEW562Mark Harris
1761CS12821Mark Harris
1771PIO004Mark Harris
1781BUI019Mark Harris
1791BUI019Mark Harris
1801BUI019Mark Harris
1811ROO013Mark Harris
1821CS2922Mark Harris
1831ALM010Mark Harris
1841BUI019Mark Harris
1851ALM010Mark Harris
1861CS24488Mark Harris
1871CS24488Mark Harris
1881ALL066Mark Harris
1891YES001Mark Harris
1901CS2122Mark Harris
1911BUI019Mark Harris
1921CS24488Mark Harris
1931WAR037Mark Harris
1941CS24488Mark Harris
1951ROO013Mark Harris
1961NCB001Mark Harris
1971ONE009Mark Harris
1981PDT001Mark Harris
1991TRA692Mark Harris
2001BEN3822Mark Harris
2011TRA999Mark Harris
2021WOL164Mark Harris
2031CS12821Mark Harris
2041JAC020Mark Harris
2051CS11288Mark Harris
2061CS15408Mark Harris
2071ROO013Mark Harris
2081CS15408Mark Harris
2091CS15408Mark Harris
2101CS24488Mark Harris
2111CS12821Mark Harris
2121ONE009Mark Harris
2131HUW243Mark Harris
2141CS24488Mark Harris
2151WYC004Mark Harris
2161CS11288Mark Harris
2171CS11288Mark Harris
2181SUR013Mark Harris
2191SUR013Mark Harris
2201WRE005Mark Harris
2211BUI019Mark Harris
2221BEN042Mark Harris
2231LON029Mark Harris
2241CS15408Mark Harris
2251CS12976Mark Harris
Sheet1
Cell Formulas
RangeFormula
BK127BK127=SUM(IF(FREQUENCY(IF((BI127:BI225="BDM - Nick")*(BJ127:BJ225<>"")*(MONTH(BJ127:BJ225)=1),MATCH(BH127:BH225,BH127:BH225,0)),ROW(BH127:BH225)-ROW(BH127)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Since you have MS 365 here is another option.
Based on your sample I have assumed all the dates are the 1st of the month. If that is not the case let me know and I will modify the formula which will just make it a bit longer

Excel Formula: 
=COUNTA( UNIQUE( FILTER($BH$127:$BH$225, ($BI$127:$BI$225="BDM - Nick") * ($BJ$127:$BJ$225 =  DATE(2024,1,1)),"") ) )
 
Upvote 0
Is it this?
Excel Formula: 
=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(MONTH(bj)=1)*(bj<>"")*(BI127:BI225="BDM - Nick"),NA()))),0))

@Alex Blakenburg
Alex if no rows meet the criteria that formula would still return a result of 1
 
Upvote 0
Another option might be
Excel Formula: 
=IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(TEXT(BJ127:BJ225,"dm")="11")*(BI127:BI225="BDM - Nick"),NA()))),0)
 
Upvote 0
hrayani said:
Hello,

How about

Book1
BHBIBJBK
1271ONE009BDM - Nick1-Feb-243
1281BUI019BDM - Nick1-Jan-24
1291BUI019BDM - Nick1-Mar-24
1301BUI019BDM - Nick1-Jan-24
1311BUI019BDM - Nick1-Jan-24
1321BUI019BDM - Nick1-Jan-24
1331BUI019BDM - Nick1-Jan-24
1341ONE009BDM - Nick1-Jan-24
1351WYC004BDM - Nick1-Jan-24
1361YES001Mark Harris1-Jan-24
1371ONE009Mark Harris
1381BUI019Mark Harris
1391BUI019Mark Harris
1401BUI019Mark Harris
1411CS12821Mark Harris
1421CS2922Mark Harris
1431CS11288Mark Harris
1441ONE009Mark Harris
1451BUI019Mark Harris
1461ONE009Mark Harris
1471WRE005Mark Harris
1481CS12821Mark Harris
1491YES001Mark Harris
1501CS2922Mark Harris
1511JAC020Mark Harris
1521JAC020Mark Harris
1531ROO013Mark Harris
1541ROO013Mark Harris
1551BUI019Mark Harris
1561CS12821Mark Harris
1571BUI019Mark Harris
1581PRS002Mark Harris
1591HUW242Mark Harris
1601GAM005Mark Harris
1611MBS003Mark Harris
1621BUI019Mark Harris
1631BUR031Mark Harris
1641WAR037Mark Harris
1651CS11288Mark Harris
1661CS11288Mark Harris
1671ONW001Mark Harris
1681CS15408Mark Harris
1691BUI019Mark Harris
1701BUI019Mark Harris
1711TOP306Mark Harris
1721ONE009Mark Harris
1731BEN3844Mark Harris
1741ONE009Mark Harris
1753JEW562Mark Harris
1761CS12821Mark Harris
1771PIO004Mark Harris
1781BUI019Mark Harris
1791BUI019Mark Harris
1801BUI019Mark Harris
1811ROO013Mark Harris
1821CS2922Mark Harris
1831ALM010Mark Harris
1841BUI019Mark Harris
1851ALM010Mark Harris
1861CS24488Mark Harris
1871CS24488Mark Harris
1881ALL066Mark Harris
1891YES001Mark Harris
1901CS2122Mark Harris
1911BUI019Mark Harris
1921CS24488Mark Harris
1931WAR037Mark Harris
1941CS24488Mark Harris
1951ROO013Mark Harris
1961NCB001Mark Harris
1971ONE009Mark Harris
1981PDT001Mark Harris
1991TRA692Mark Harris
2001BEN3822Mark Harris
2011TRA999Mark Harris
2021WOL164Mark Harris
2031CS12821Mark Harris
2041JAC020Mark Harris
2051CS11288Mark Harris
2061CS15408Mark Harris
2071ROO013Mark Harris
2081CS15408Mark Harris
2091CS15408Mark Harris
2101CS24488Mark Harris
2111CS12821Mark Harris
2121ONE009Mark Harris
2131HUW243Mark Harris
2141CS24488Mark Harris
2151WYC004Mark Harris
2161CS11288Mark Harris
2171CS11288Mark Harris
2181SUR013Mark Harris
2191SUR013Mark Harris
2201WRE005Mark Harris
2211BUI019Mark Harris
2221BEN042Mark Harris
2231LON029Mark Harris
2241CS15408Mark Harris
2251CS12976Mark Harris
Sheet1
Cell Formulas
RangeFormula
BK127BK127=SUM(IF(FREQUENCY(IF((BI127:BI225="BDM - Nick")*(BJ127:BJ225<>"")*(MONTH(BJ127:BJ225)=1),MATCH(BH127:BH225,BH127:BH225,0)),ROW(BH127:BH225)-ROW(BH127)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
Hi Hrayani,
This worked but i realised i need to add a column (BG) that says one of 3 things. "Sales Order", "Sales Quote" or "Sales-i". In the formula you've given me, how do i add so it matches what you've done already but also counts if BG sales "Sales Order".
 
Upvote 0
Alex Blakenburg said:
Since you have MS 365 here is another option.
Based on your sample I have assumed all the dates are the 1st of the month. If that is not the case let me know and I will modify the formula which will just make it a bit longer

Excel Formula: 
=COUNTA( UNIQUE( FILTER($BH$127:$BH$225, ($BI$127:$BI$225="BDM - Nick") * ($BJ$127:$BJ$225 =  DATE(2024,1,1)),"") ) )
Click to expand...
Hi Alex,
It could be any day of the month. With this formula i got a result showing 1?
 
Upvote 0
monkeyharris said:
It could be any day of the month.
Click to expand...
monkeyharris said:
i need to add a column (BG) that says one of 3 things. "Sales Order", "Sales Quote" or "Sales-i". .... but also counts if BG sales "Sales Order".
Click to expand...

Is this what you mean? (Some mark Harris rows hidden)

24 03 30.xlsm
BGBHBIBJBKBLBM
127Sales Order1ONE009BDM - Nick01-FebOriginal Qn post 4With new condition
128Sales Order1BUI019BDM - Nick01-Jan32
129Sales Order1BUI019BDM - Nick01-Mar
130Sales Order1BUI019BDM - Nick21-Jan
131Sales-i1BUI019BDM - Nick01-Jan
132Sales Quote1BUI019BDM - Nick01-Jan
133Sales Order1BUI019BDM - Nick01-Jan
134Sales Order1ONE009BDM - Nick22-Jan
135Sales Quote1WYC004BDM - Nick01-Jan
136Sales Order1YES001Mark Harris01-Jan
137Sales Order1ONE009Mark Harris
138Sales Order1BUI019Mark Harris
139Sales Order1BUI019Mark Harris
140Sales Order1BUI019Mark Harris
141Sales Order1CS12821Mark Harris
142Sales Order1CS2922Mark Harris
143Sales Order1CS11288Mark Harris
144Sales Order1ONE009Mark Harris
220Sales Order1WRE005Mark Harris
221Sales Order1BUI019Mark Harris
222Sales Order1BEN042Mark Harris
223Sales Order1LON029Mark Harris
224Sales Order1CS15408Mark Harris
225Sales Order1CS12976Mark Harris
Count
Cell Formulas
RangeFormula
BL128BL128=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(MONTH(bj)=1)*(bj<>"")*(BI127:BI225="BDM - Nick"),NA()))),0))
BM128BM128=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(MONTH(bj)=1)*(bj<>"")*(BI127:BI225="BDM - Nick")*(BG127:BG225="Sales Order"),NA()))),0))
 
Upvote 0
Solution
Peter_SSs said:
Is this what you mean? (Some mark Harris rows hidden)

24 03 30.xlsm
BGBHBIBJBKBLBM
127Sales Order1ONE009BDM - Nick01-FebOriginal Qn post 4With new condition
128Sales Order1BUI019BDM - Nick01-Jan32
129Sales Order1BUI019BDM - Nick01-Mar
130Sales Order1BUI019BDM - Nick21-Jan
131Sales-i1BUI019BDM - Nick01-Jan
132Sales Quote1BUI019BDM - Nick01-Jan
133Sales Order1BUI019BDM - Nick01-Jan
134Sales Order1ONE009BDM - Nick22-Jan
135Sales Quote1WYC004BDM - Nick01-Jan
136Sales Order1YES001Mark Harris01-Jan
137Sales Order1ONE009Mark Harris
138Sales Order1BUI019Mark Harris
139Sales Order1BUI019Mark Harris
140Sales Order1BUI019Mark Harris
141Sales Order1CS12821Mark Harris
142Sales Order1CS2922Mark Harris
143Sales Order1CS11288Mark Harris
144Sales Order1ONE009Mark Harris
220Sales Order1WRE005Mark Harris
221Sales Order1BUI019Mark Harris
222Sales Order1BEN042Mark Harris
223Sales Order1LON029Mark Harris
224Sales Order1CS15408Mark Harris
225Sales Order1CS12976Mark Harris
Count
Cell Formulas
RangeFormula
BL128BL128=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(MONTH(bj)=1)*(bj<>"")*(BI127:BI225="BDM - Nick"),NA()))),0))
BM128BM128=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(MONTH(bj)=1)*(bj<>"")*(BI127:BI225="BDM - Nick")*(BG127:BG225="Sales Order"),NA()))),0))
Click to expand...
Checking it now Peter.
 
Upvote 0
monkeyharris said:
Checking it now Peter.
Click to expand...
Peter, quick question whilst i try this. The other day you gave me this formula: LET(r,YTD_Figures!B2:B5789,IFNA(ROWS(UNIQUE(FILTER(r,(r<>"")*(YTD_Figures!G2:G5789=""),NA()))),0))
It worked for what i was doing, but when i close the workbook and reopen, the formula is ALWAYS replaced with {=#VALUE!} which won't work, so i have to put the formula in again?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Count of consecutive months same data value appears
Replies
1
Views
209
rhonllanders
R
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
517
AlfredDB
A
P
  • Solved
How to count all unique values based on another column
Replies
6
Views
2K
Fluff
Fluff
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
256
Peter_SSs
Peter_SSs
P
  • Question Question
Summing Data based on a count of prior month data
2
Replies
12
Views
1K
KRice
K

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top