Counting The Occurances Of A Frequency or Mode

fla5hbak

New Member
Joined
Jan 18, 2005
Messages
12
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
Joined
Jan 18, 2005
Messages
12
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
Joined
Jan 18, 2005
Messages
12
Oh crap. Sorry. Didnt read the last bit of your post! *SLAPS FOREHEAD*

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

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

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
Top