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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sorry to jump on a thread but this is the closest to my question.

How can i sum the number of different values within a column that follow 2 different criteria.

below is my example, on the right is the result that I wan, I am lookoing for a formula to give me those results.
I need to know the number of different types of grades each fruit has per month,

[TABLE="width: 432"]
<COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]fruit[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]grade[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]month[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]good[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]june[/TD]
[TD="bgcolor: transparent"]july[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]good[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]bad[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]good[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]goor[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]bad[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]poor[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]good[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]bad[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apple[/TD]
[TD="bgcolor: transparent"]good[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]goor[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]bad[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]banana[/TD]
[TD="bgcolor: transparent"]poor[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]fine[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]pear[/TD]
[TD="bgcolor: transparent"]poor[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]



hoping anyone can help
 
Upvote 0
You can use countifs code:

COUNTIFS($A$2:$A$21,$E$3,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$3,$C$2:$C$21,$G$2)

COUNTIFS($A$2:$A$21,$E$4,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$4,$C$2:$C$21,$G$2)

COUNTIFS($A$2:$A$21,$E$5,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$5,$C$2:$C$21,$G$2)

Paste the codes is relvant cells

Is this what you are looking for?
 
Upvote 0
@nightshade

A:C houses the data and the processing is located in E:G.

F2, control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$21=$E3,IF($C$2:$C$21=F$2,IF($B$2:$B$21<>"",
  MATCH($B$2:$B$21,$B$2:$B$21,0)))),ROW($B$2:$B$21)-ROW($B$2)+1),1))
 
Upvote 0
RIZVI said:
You can use countifs code:

COUNTIFS($A$2:$A$21,$E$3,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$3,$C$2:$C$21,$G$2)

COUNTIFS($A$2:$A$21,$E$4,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$4,$C$2:$C$21,$G$2)

COUNTIFS($A$2:$A$21,$E$5,$C$2:$C$21,$F$2)
COUNTIFS($A$2:$A$21,$E$5,$C$2:$C$21,$G$2)

Paste the codes is relvant cells

Is this what you are looking for?
Click to expand...

countifs pulled the total numern (i.e. the apple/june returned a value of 4 instead of the expeted 2)
:(
 
Upvote 0
Aladin Akyurek said:
@nightshade

A:C houses the data and the processing is located in E:G.

F2, control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$21=$E3,IF($C$2:$C$21=F$2,IF($B$2:$B$21<>"",
  MATCH($B$2:$B$21,$B$2:$B$21,0)))),ROW($B$2:$B$21)-ROW($B$2)+1),1))
Click to expand...

worked just fine, (well, it returned the expected value),
could i use a Name for range i.e. B2:B21 is 'grade', and substitute in the formula?

I ask because my raw data sits on a differnt sheet than the results table
 
Upvote 0
note: this worked "=SUM(IF(FREQUENCY(IF(fruit=$E5,IF(month=G$2,IF(grade<>"", MATCH(grade,grade,0)))),ROW(grade)-ROW($B$2)+1),1))"
(as an array)

which is brillaint!! (although i still don't understand $B$2)!
 
Upvote 0
night5hade said:
worked just fine, (well, it returned the expected value),
could i use a Name for range i.e. B2:B21 is 'grade', and substitute in the formula?

I ask because my raw data sits on a differnt sheet than the results table
Click to expand...

Yes, name each range as appropriate: fruit, grade, and month. The formula becomes:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF(fruit=$E3,IF(month=F$2,IF(grade<>"",
  MATCH(grade,grade,0)))),ROW(grade)-ROW(INDEX(grade,1,1))+1),1))
Control+shift+enter still applies...
 
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,931
Messages
6,175,465
Members
452,645
Latest member
Tante

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