Count only cells with specific formula element

[CMPeterson]

Hi!

I'm working on an analysis of a frequency distribution that is based on sums of unique combinations of numbers, and I want to find out how many cells have a specific number in the calculation. For example: If I have two cells, one of which is =1+2+3+4+5, and another cell that is =2+4+6+8+10, then the count for 2 as an element is '2', while the count for 1 as an element is '1' (10 is a different element from 1). Which formula should I use to give me this count automatically? I've tried using the COUNT function, Pivot Tables and vlookups, but these only seem to count the entire cell's display, not elements within a calculation.

Thanks!
Catherine

[tim201110]

maybe you should't use =2+4+6+8+10?

[CMPeterson]

That is not helpful. What would you recommend that I use instead?

[AliGW]

You will need to provide some sample data with expected outcomes, please.

[CMPeterson]

This is what I'm trying to do:

1) Build a frequency distribution of all sums possible when choosing and adding 5 numbers drawn from a set of 1 - 10, without replacement (e.g. 1+2+3+4+5 = 15, where '15' is displayed in the cell).
2) Find out how many of these sums (e.g. 15 from #1) are calculated using a specific number from the set (e.g. 4).
3) Ideally, be able to adjust #2 so that I can focus on sums within a specific range (e.g. between 20 & 30), and how many of those are built using a specific number (e.g. 4).

[AliGW]

I don't think it's at all necessary for these elements to be in the same cell from what you have said, however some sample data mocked up to show what you are aiming for (a few rows of results data with the source data) will help us to advise you.

[CMPeterson]

Here is a sample distribution, where each cell in the spreadsheet would display the sum (listed in the first row as value = #):

Value = 6	Value = 7	Value = 8	Value = 9	Value = 10	Value = 11	Value = 12
=1+2+3	=1+2+4	=1+2+5	=1+3+5	=1+4+5	=2+4+5	=3+4+5
		=1+3+4	=2+3+4	=2+3+5

Here is what I am trying to build from that data, using only the calculations that give values from 7 to 9:

# sums using 1	4
# sums using 2	3
# sums using 3	3
# sums using 4	3
# sums using 5	2

Is there a way to do this automatically, without having to manually count each and every cell that includes a specific number in the summation?

[tim201110]

is there VBA in mac?

Please Login or Register  to view this content.

[CMPeterson]

Yes, there is a VB editor in the Mac Excel, but I've never used it. How does it work?

[JeteMc]

Hello CMPeterson and Welcome to Excel Forum.
Here is a formula based proposal that takes a bit of manipulating the formulas from which the count is taken:
1. I pasted the formulas from post #7 into rows 1:3
2. On the Formulas tab I selected Show Formulas
3. I utilized the Replace feature to replace = with '=
4. I deselected Show Formulas
5. I typed the digits 1:9 into A5:A13
6. I placed the following formula into cell B5 and copied down: =SUMPRODUCT(--ISNUMBER(SEARCH(TEXT(A5,"0"),Sheet1!A$2:G$3)))
For future reference you will usually get faster results if you upload a workbook that displays your query by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[CMPeterson]

Thanks! I think I see how to adjust that formula to make it more versatile, in the context I need, but it at least gives me a starting point.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.