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

M

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
358
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))
 
Sort by date Sort by votes

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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...
Thanks Peter, that worked perfectly. If i wanted to make the calculation look for months 1, 2 and 3 so checking the first quarter, would i just use the entire formula 3 times with a + in between and change the month to 1, 2 and 3 on the last one.
 
Upvote 0
monkeyharris said:
months 1, 2 and 3 so checking the first quarter
Click to expand...
Try this. Change the red 1 to 2 for the second quarter (ie months 4,5,6) to 3 for the third quarter etc

=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(INT((MONTH(bj)-1)/3)+1=1)*(bj<>"")*(BI127:BI225="BDM - Nick")*(BG127:BG225="Sales Order"),NA()))),0))
 
Upvote 0
In future please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. The mark has been changed in this thread so no further action is required this time.
 
Upvote 0
Peter_SSs said:
Try this. Change the red 1 to 2 for the second quarter (ie months 4,5,6) to 3 for the third quarter etc

=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(INT((MONTH(bj)-1)/3)+1=1)*(bj<>"")*(BI127:BI225="BDM - Nick")*(BG127:BG225="Sales Order"),NA()))),0))
Click to expand...
Hi Peter,
This formula didn't work, it wouldn't let me enter out of the formula and kept sending the curser to where it says NA() at the end. I had to press escape to come out formula.
 
Upvote 0
monkeyharris said:
Hi Peter,
This formula didn't work, it wouldn't let me enter out of the formula and kept sending the curser to where it says NA() at the end. I had to press escape to come out formula.
Click to expand...
Could it be a similar problem to the issue discovered in your other thread?

Here it is working for me. The green rows meet the basic conditions of Date in first quarter, "BDM - Nick" in col BI and "Sales Order in col BG. Then count the unique values which are the bright blue ones.

24 03 30.xlsm
BGBHBIBJBKBM
126
127Sales Order1ONE009BDM - Nick01-FebQuarter 1
128Sales Order1BUI019BDM - Nick01-Jan2
129Sales Order1BUI019BDM - Nick01-Mar
130Sales Order1BUI019BDM - Nick21-Jan
131Sales-i1BUI019BDM - Nick01-Jan
132Sales Quote1BUI019BDM - Nick01-Jan
133Sales Order1BUI019BDM - Nick01-Dec
134Sales Order1ONE009BDM - Nick22-Feb
135Sales Order1WYC004BDM - Nick21-Apr
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
226
Count
Cell Formulas
RangeFormula
BM128BM128=LET(bj,BJ127:BJ225,IFNA(ROWS(UNIQUE(FILTER(BH127:BH225,(INT((MONTH(bj)-1)/3)+1=1)*(bj<>"")*(BI127:BI225="BDM - Nick")*(BG127:BG225="Sales Order"),NA()))),0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to count all unique values based on another column
Replies
6
Views
666
Fluff
Fluff
P
  • Question
Summing Data based on a count of prior month data
2
Replies
12
Views
665
KRice
K
Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
188
zero269
Z
M
  • Question
Count unique cells based on column of blank cells
2
Replies
13
Views
286
Peter_SSs
Peter_SSs
G
  • Question
Countif by month and part and distinct values
Replies
2
Views
547
Greymud
G

Forum statistics

Threads
1,215,209
Messages
6,123,646
Members
449,111
Latest member
ghennedy

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