# Frequency * Time histogram data

#### Measels

##### New Member
Hi,

I have searched far and wide for a solution to this and spent hours trying to figure it out, and I am now admiting defeat and hoping someone will be able to help me.

I have the following type of data:

Example dataset:
 A B C D E 1 Level Time Bin Range Level Frequency Level Frequency * Time 2 1.2 40 0 2 3 3.5 100 1 0 4 3.9 30 2 0 5 2.8 130 3 3 6 1.6 50 4 1 7 6.2 80 5 0 8 3.4 10 6 1 9 4.7 240 7 0

<tbody>
</tbody>

The real data is more substantial (over 10,000 cells worth), this is just to illustrate.

I have generated the frequencies using the following formula "=COUNTIF(A:A,"<"&C3)-COUNTIF(A:A,"<"&C2)". This has worked fine and I can generate a histogram from this.

The problem is that in addition to this, I would like to look not just at the frequency of the level appearing, but to generate a histogram of time spent at that level. I.E. I am trying to come up with a formula that looks at the column with the levels (column A) if the value in column A falls in the correct range, then the corresponding value in column B would be recorded and summed.

Not sure if I explained that too well. For this small data set, I can of course fill in the values manually, and they would come out as shown below:

Example manually calculated solution:
 A B C D E 1 Level Time Bin Range Level Frequency Level Frequency * Time 2 1.2 40 0 2 90 3 3.5 100 1 0 40 4 3.9 30 2 0 0 5 2.8 130 3 3 140 6 1.6 50 4 1 240 7 6.2 80 5 0 0 8 3.4 10 6 1 80 9 4.7 240 7 0 0

<tbody>
</tbody>

Thank you in advance for any suggestions to do this! It's driving me crazy!

### 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.
A SumIfs works in this case.

Code:
``=SUMIFS(B:B,A:A,">" & C2,A:A,"<" & C3)``

Thank you so much, that worked brilliantly, and so much more simple than the various things I was trying.

Thanks again!

No, problem bud happy to help

Replies
1
Views
133
Replies
0
Views
119
Replies
3
Views
802
Replies
1
Views
493
Replies
7
Views
200

1,196,360
Messages
6,014,814
Members
441,847
Latest member
hw407

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