Frequency * Time histogram data

[Measels]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Level[/TD]
[TD]Time[/TD]
[TD]Bin Range[/TD]
[TD]Level Frequency[/TD]
[TD]Level Frequency * Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.2[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3.5[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3.9[/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2.8[/TD]
[TD]130[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1.6[/TD]
[TD]50[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6.2[/TD]
[TD]80[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3.4[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4.7[/TD]
[TD]240[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Level[/TD]
[TD]Time[/TD]
[TD]Bin Range[/TD]
[TD]Level Frequency[/TD]
[TD]Level Frequency * Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.2[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3.5[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3.9[/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2.8[/TD]
[TD]130[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1.6[/TD]
[TD]50[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6.2[/TD]
[TD]80[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3.4[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4.7[/TD]
[TD]240[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[friel300]

A SumIfs works in this case.

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

 

[Measels]

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

Thanks again!

 

[friel300]

No, problem bud happy to help