Count If with multiple criteria and unique values

D

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.
 
Sort by date Sort by votes
barry houdini said:
Welcome to MrExcel Brothwood,

You have a comma in place of a parenthesis.....and you really should restrict the range because these type of formulas can be slow when referencing whole columns, try this version

=SUM(IF(FREQUENCY(IF(A2:A1000=6,IF(B2:B1000="ALPHA",IF(C2:C1000<>"",MATCH(C2:C1000,C2:C1000,0)))),ROW(C2:C1000)-MIN(ROW(C2:C1000))+1),1))

confirmed with CTRL+SHIFT+ENTER

adjust ranges if required
Click to expand...

Thank you so much, that fixed it, i think i was just looking at the code so much i didnt notice my mistake.

Thank you again
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Brothwood said:
Thank you so much, that fixed it, i think i was just looking at the code so much i didnt notice my mistake.

Thank you again
Click to expand...

You can avoid the MIN function call though...

=SUM(IF(FREQUENCY(IF(A2:A1000=6,IF(B2:B1000="ALPHA",IF(C2:C1000<>"",MATCH(C2:C1000,C2:C1000,0)))),ROW(C2:C1000)-ROW(C2)+1),1))
 
Upvote 0
Aladain i see you are god in these parts as such i seek information.
I need to output totals per user and per month.

Input data

DATE DRAWN
 
Upvote 0
Aladin i see you are god among these parts hence i seek favor.

Inputs are: (test)

DATE DRAWN</SPAN>
DRAWN BY</SPAN>
18/04/2015</SPAN>
G.APUWAI</SPAN>
18/04/2015</SPAN>
A.LEONARDI</SPAN>
18/04/2015</SPAN>
B.GOLDSWORTHY</SPAN>
18/04/2015</SPAN>
C.HOPNER</SPAN>
18/04/2015</SPAN>
G.WOODALL</SPAN>
18/04/2015</SPAN>
G.TARLINTON</SPAN>
18/04/2015</SPAN>
I.SAVAGE</SPAN>
18/04/2015</SPAN>
J.BECK</SPAN>
18/04/2015</SPAN>
P.JONES</SPAN>
18/04/2015</SPAN>
P.PEARSON</SPAN>
18/04/2015</SPAN>
R.BULLIMORE</SPAN>
18/04/2015</SPAN>
S.VENITIS</SPAN>
18/04/2015</SPAN>
S.CONSTABLE</SPAN>
18/04/2015</SPAN>
S.NIELSEN</SPAN>
18/04/2015</SPAN>
S.BUCHHOLZ</SPAN>
18/04/2015</SPAN>
S.BUCHHOLZ</SPAN>

<TBODY>
</TBODY>

on a new sheet named 'MONTHLY REPORT' i hope to populate this:

[TABLE="width: 616"]
<TBODY>[TR]
[TD="colspan: 3"]MONTHLY REPORT</SPAN>[/TD]
[/TR]
[TR]
[TD]APRIL </SPAN></SPAN>[/TD]
[TD="colspan: 2"]32</SPAN>[/TD]
[/TR]
[TR]
[TD]A.LEONARDI</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B.GOLDSWORTHY</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C.HOPNER</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G.WOODALL</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G.TARLINTON</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I.SAVAGE</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J.BECK</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.JONES</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.PEARSON</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R.BULLIMORE</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S.VENITIS</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S.CONSTABLE</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S.NIELSEN</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S.BUCHHOLZ</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G.APUWAI</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Currently i am using a previously posted formula

Code: 
=SUM(IF(FREQUENCY(IF('AS CON LIST'!$C$3:$C$9001<>"",IF('AS CON LIST'!$G$3:$G$9001=D4,
    MATCH("~"&'AS CON LIST'!$C$3:$C$9001,'AS CON LIST'!$C$3:$C$9001&"",0))),
       ROW('AS CON LIST'!$C$3:$C$9001)-ROW('AS CON LIST'!G3)+1),1))
alas i cant work out how to add a date range!
however it does work perfect in its current form just doesnt allow for monthly filtering yet.
Many thanks in advance
Greenuser
 
Upvote 0
The lay-out you posted does not correspond to the the input data.

Are you wanting to create a unique list of customers on the monthly report with respect to the month April and count how often each appear in that month?
 
Upvote 0
Morning Aladin.
Correct that other criteria wil be an a unique address which shouldnt be duplicated in the cell range.
The data is yet to be populated for may june july etc.
but the drawn by will remain the same in each period and i will need an individual count for each operator (drawn by) (possibly zero if that makes it hard?).
atm i have am using a drop down box for the said drawn by cells
but have the list referenced to another sheet.
[TABLE="width: 919"]
<TBODY>[TR]
[TD](c3)ADDRESS</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD](F3)DATE DRAWN</SPAN>[/TD]
[TD](G3)DRAWN BY</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] G.APUWAI</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015 </SPAN>[/TD]
[TD]A.LEONARDI</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] B.GOLDSWORTHY</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015 </SPAN>[/TD]
[TD]C.HOPNER</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] G.WOODALL</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] G.TARLINTON</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] I.SAVAGE</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] J.BECK</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] P.JONES</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] P.PEARSON</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015 </SPAN>[/TD]
[TD]R.BULLIMORE</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015 </SPAN>[/TD]
[TD]S.VENITIS</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]13</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD] S.CONSTABLE</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]14</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD]S.NIELSEN</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]15</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD]S.BUCHHOLZ</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]16</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/04/2015</SPAN>[/TD]
[TD]S.BUCHHOLZ</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
I have done lots of searching and seem ot be close with this topic but didnt see exactly what i was looking for. I really hope someone here can help me out. Basic layout of my sheet below.

Jan Y N Y Y N Y Results: Jan = 8
Jan Y Y Y N N N Feb = 0
Feb N N N N N N Mar = 4
Mar Y Y N Y N Y
Jan Y N N N N N

Basically I need to count the Y's in the range for each month in column A.

Thanks for any insight you can provide.
 
Upvote 0
juicbox said:
Basically I need to count the Y's in the range for each month in column A.
Click to expand...

Something like this maybe:


Excel 2012
ABCDEFGHIJ
1JanYNYYNYJan8
2JanYYYNNNFeb0
3FebNNNNNNMar4
4MarYYNYNY
5JanYNNNNN
Sheet1
Cell Formulas
RangeFormula
J1=SUMPRODUCT(($B$1:$G$5="Y")*($A$1:$A$5=I1))
 
Upvote 0
Thank you so very much!!!! Guess I was trying to put too much thought into it. This formula works great however my sheet is very large and when I use it i keep getting an error that Excel is using too many resources so it can not calculate the values. Not sure if it is the size of the Y/N range or that I'm using the formula on another sheet.
 
Upvote 0
How big is the range? If you use a helper column is performance improved any?


Excel 2012
ABCDEFGHIJK
1JanYNYYNY4Jan8
2JanYYYNNN3Feb0
3FebNNNNNN0Mar4
4MarYYNYNY4
5JanYNNNNN1
Sheet1
Cell Formulas
RangeFormula
H1=COUNTIF(B1:G1,"Y")
K1=SUMIF(A:A,J1,H:H)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
R
  • Question Question
Countifs Multiple Criteria
Replies
3
Views
326
Robot1
R
Z
  • Question Question
Vba - create new sheet and copy date into it.
Replies
1
Views
69
zerofeng
Z
D
  • Solved
Formula for column for (sort of) running count
Replies
7
Views
110
Peter_SSs
Peter_SSs
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,935
Messages
6,175,494
Members
452,649
Latest member
mr_bhavesh

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