# Counting The Occurances Of A Frequency or Mode

#### fla5hbak

##### New Member
Hi

Found these boards after scratching my head over a Excel sheet for a few hours. I hope you can help me. I have been searching through the messages but cannot seem to find the required information.

I have a spreadsheet, on which there is a presentation page. This presentation page is to be automatically updated using formulas to reference other pages within the workbook.

So, I want to find the frequency of a number that is based on two criteria, "ctype" and "group". The frequency bit is called "setting", and all of these are numerical data.

I want to cross reference the "group" and "ctype" data to find which number occurs most frequently in the "setting" column, then count how many instances of that number exist within that cross-reference.

I figure this can be done using the CSE type method, but I cannot figure out the syntax of executing it.

Any help would be appreciated. Thanks

Trefor

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### fairwinds

##### MrExcel MVP
Welcome to the board!

Try:
F4: =MODE(IF((A2:A10=E1)*(B2:B10=E2),C2:C10))
confirmed with Ctrl + shift + enter.

F5: =SUMPRODUCT(--(A2:A10=E1),--(B2:B10=E2),--(C2:C10=F4))
normal entered
Book2.xls
ABCDEF
1TypeGroupSettingaaa
2aaaxxx5xxx
3aaayyy7
4aaaxxx5Mode5
5aaayyy7Occurances2
6aaaxxx7
7aaayyy7
8aaaxxx7
9bbbyyy7
10bbbxxx7
Sheet4

#### fla5hbak

##### New Member
Er. I wasnt expecting THAT quicka reply. Thanks!

I have altered the formula you provided to read:

=MODE(IF((ctype=1)*(group=1),setting))

as I have used column naming to make things a bit easier on the eyes.

Now, it half worked, as it returned a value of 4, which is the mode of selected and filtered data. What I need now is to count how many times that number occurs within the "setting" column, while still meeting the "ctype" and "group" criteria.

=)

Can you provide any further insight?

#### fla5hbak

##### New Member

Will give it a whirl now. Thanks again. =)

#### fairwinds

##### MrExcel MVP
Check out the SUMPRODUCT formula I posted above.

EDIT: So you did.

Replies
8
Views
2K
Replies
4
Views
501
Replies
5
Views
272
Replies
13
Views
635
Replies
2
Views
459

1,181,993
Messages
5,933,145
Members
436,881
Latest member
CMScons

### 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.

### Which adblocker are you using?

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

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