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
GREENUSER said:
Aladin essentially i need to add a date range to this code: of possible.



Forumula is located in L5:L10 gives total at the moment! i want it to give the total for a set date range,per month!
K5:K10 contains the users list
'AS CON LIST'!D3:D1400 contains the Users and is the value i wish to count
'AS CON LIST'! C3:C1400 contains the date .

I can get the results i want by manually selecting range as the date is chronological however for excersise sake id like to use code featuring date range!

here is a mock table i wish to furfill i hope i have been thorough enough
Thanks in advance your input is much appreciated!


[TABLE="width: 540"]
<tbody>[TR]
[TD]DATE DRAWN
[/TD]
[TD]DRAWN BY
[/TD]
[TD]January
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015
[/TD]
[TD]G.APUWAI
[/TD]
[TD]Inspector
[/TD]
[TD]Total drawn
[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015
[/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD]G.APUWAI
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/2015
[/TD]
[TD]G.WOODALL
[/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]4/02/2015
[/TD]
[TD]C.HOPNER
[/TD]
[TD]G.WOODALL
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]4/06/2015
[/TD]
[TD]P.JONES
[/TD]
[TD]C.HOPNER
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4/05/2015
[/TD]
[TD]R.BULLIMORE
[/TD]
[TD]P.JONES
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4/03/2015
[/TD]
[TD]P.PEARSON
[/TD]
[TD]R.BULLIMORE
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5/03/2015
[/TD]
[TD]G.APUWAI
[/TD]
[TD]P.PEARSON
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5/06/2015
[/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2015
[/TD]
[TD]G.WOODALL
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Would you explain what 3 means? And do we have three 1's, that is, what kind of count are these values?
 
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.
Its a work log counting a completed Job!
The 3 is the total for the month of january im planning on doing a monthly report! just a sum of the total drawn column!
The one (1) is that total drawings for that month for each inspector.
 
Upvote 0
GREENUSER said:
Its a work log counting a completed Job!
The 3 is the total for the month of january im planning on doing a monthly report! just a sum of the total drawn column!
The one (1) is that total drawings for that month for each inspector.
Click to expand...

How did you get that 3? And how do you get 1 for G.APUWAI?
 
Upvote 0
Aladin Akyurek said:
How did you get that 3? And how do you get 1 for G.APUWAI?
Click to expand...

The 3 is the sum of the drawings completed by all the inspectors for january!
in columb B we see that G.Apuwai logged a job on the 4th of janurary. perhaps its the date system we use in austalia that throws off the data?
Thats why we have a 1 in the inspectors tally!
obviously this is a shortened versions but the fofmular should apply to that range and criteria.
Namely date to check will be A2:A1500 Inspector will be B2;B1500 and i want a count function featuring a date range produced for each individual inspector!
 
Upvote 0
GREENUSER said:
The 3 is the sum of the drawings completed by all the inspectors for january!
in columb B we see that G.Apuwai logged a job on the 4th of janurary. perhaps its the date system we use in austalia that throws off the data?
Thats why we have a 1 in the inspectors tally!
obviously this is a shortened versions but the fofmular should apply to that range and criteria.
Namely date to check will be A2:A1500 Inspector will be B2;B1500 and i want a count function featuring a date range produced for each individual inspector!
Click to expand...

Suppose we have two additional entry in A for G.APUWAI in January: 1-Jan-2015 and 7-Jan-2015. What would be the score of G.APUWAI?
 
Upvote 0
GREENUSER said:
3 .
and in the total next to january it will say 5!
Click to expand...

Your questions, as now evidenced, have nothing to do with unique counting, which is the subject of this lengthy thread!...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td]DATE DRAWN[/td][td]DRAWN BY[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]January[/td][td]
3​
[/td][/tr]

[tr][td]
3​
[/td][td]
1/4/2015​
[/td][td]G.APUWAI[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Inspector[/td][td]Total drawn[/td][/tr]

[tr][td]
4​
[/td][td]
1/4/2015​
[/td][td]B.GOLDSWORTHY[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]G.APUWAI[/td][td]
1​
[/td][/tr]

[tr][td]
5​
[/td][td]
1/4/2015​
[/td][td]G.WOODALL[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B.GOLDSWORTHY[/td][td]
1​
[/td][/tr]

[tr][td]
6​
[/td][td]
2/4/2015​
[/td][td]C.HOPNER[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]G.WOODALL[/td][td]
1​
[/td][/tr]

[tr][td]
7​
[/td][td]
6/4/2015​
[/td][td]P.JONES[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
5/4/2015​
[/td][td]R.BULLIMORE[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
3/4/2015​
[/td][td]P.PEARSON[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
3/5/2015​
[/td][td]G.APUWAI[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
6/5/2015​
[/td][td]B.GOLDSWORTHY[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
6/15/2015​
[/td][td]G.WOODALL[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

C3:C12 is named DATEDRAWN.

D3:D12 is name DRAWNBY.

Rich (BB code): 

=ROW(DRAWNBY)-ROW(INDEX(DRAWNBY,1,1))+1
is named Ivec (that is, Ivec refers to this formula in the Name Manager.).

L2, control+shift+enter, not just enter:
Rich (BB code): 

=SUM(IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,1))

K4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IFERROR(INDEX(DRAWNBY,SMALL(IF(FREQUENCY(IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,
    IF(DRAWNBY<>"",MATCH(DRAWNBY,DRAWNBY,0))),Ivec),Ivec),ROWS(K$4:K4))),"")
This formula creates a unique list of inspectors.

L4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IF($K4="","",SUM(IF(DRAWNBY=$K4,IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,1))))
 
Upvote 0
Aladin Akyurek said:
Your questions, as now evidenced, have nothing to do with unique counting, which is the subject of this lengthy thread!...

[TABLE="class: grid"]
<TBODY>[TR]
[TD]Row\Col
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]DATE DRAWN
[/TD]
[TD]DRAWN BY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]January
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]G.APUWAI
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Inspector
[/TD]
[TD]Total drawn
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G.APUWAI
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
1/4/2015​
[/TD]
[TD]G.WOODALL
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
2/4/2015​
[/TD]
[TD]C.HOPNER
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G.WOODALL
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
6/4/2015​
[/TD]
[TD]P.JONES
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
5/4/2015​
[/TD]
[TD]R.BULLIMORE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
3/4/2015​
[/TD]
[TD]P.PEARSON
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
3/5/2015​
[/TD]
[TD]G.APUWAI
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
6/5/2015​
[/TD]
[TD]B.GOLDSWORTHY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
6/15/2015​
[/TD]
[TD]G.WOODALL
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


C3:C12 is named DATEDRAWN.

D3:D12 is name DRAWNBY.

Rich (BB code): 

=ROW(DRAWNBY)-ROW(INDEX(DRAWNBY,1,1))+1
is named Ivec (that is, Ivec refers to this formula in the Name Manager.).

L2, control+shift+enter, not just enter:
Rich (BB code): 

=SUM(IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,1))

K4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IFERROR(INDEX(DRAWNBY,SMALL(IF(FREQUENCY(IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,
    IF(DRAWNBY<>"",MATCH(DRAWNBY,DRAWNBY,0))),Ivec),Ivec),ROWS(K$4:K4))),"")
This formula creates a unique list of inspectors.

L4, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=IF($K4="","",SUM(IF(DRAWNBY=$K4,IF(DATEDRAWN-DAY(DATEDRAWN)+1=(1&$K$2)+0,1))))
Click to expand...

A bit lost at where the first line of code goes!
 
Upvote 0
I found it in another post and added to name manager under Ivec. Im still getting an error in L2 it seems to be failing when referencing to (1$K$2)
 
Upvote 0
GREENUSER said:
I found it in another post and added to name manager under Ivec. Im still getting an error in L2 it seems to be failing when referencing to (1$K$2)
Click to expand...

Select C3:C12, type DATEDRAWN in the Name Box, and hit Enter.

Select D3:D12, type DRAWNBY in the Name Box, and hit Enter.

Activate Formulas | Name Manager.
Type Ivec in the Name box.
Enter the following in the Refers to Box.

=ROW(DRAWNBY)-ROW(INDEX(DRAWNBY,1,1))+1

and hit Enter.
 
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