Hard to explain, please help

**FooFighter616** · 06-13-2012, 11:53 AM

Hello

I have a table of data showing the "flow" in one column and "hour ending" in another column. I have the formula =COUNTIF(A2:A8761,">=600") to show how many in the flow column are over capacity (i.e. over 600). What I then want to do is find out how many of this result (the number output was 974) have the "hour ending" of 1, then 2, then 3 etc up to 24. Preferably I'd like the whole thing to be done in one formula. I can't get my head around how to do this.

This is a quick extract from the spreadsheet

Flow Hr Ending
206 1
182 1
162 1
156 1

I have sorted the data into ascending order by "Hr Ending". If anyone can help I will be very grateful.

Thanks

**romperstomper** · 06-13-2012, 12:09 PM

I think you mean
=SUMPRODUCT((A2:A8761>=600)*(B2:B8761=1))

**oeldere** · 06-13-2012, 12:09 PM

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

**Cutter** · 06-13-2012, 01:26 PM

Because you have sorted your data by column B here is an alternative to SUMPRODUCT()

=COUNTIF(INDEX($A$2:$A$8761,MATCH(1,$B$2:$B$8761,0)):INDEX($A$2:$A$8761,MATCH(1,$B$2:$B$8761,0)+COUNTIF($B$2:$B$8761,1)-1),">=600")

Change the 1's in the formula to cell ref holding the hour ending value so that by changing the value in that cell the formula will recalc for the new value

**arlu1201** · 06-13-2012, 02:20 PM

Foofighter616,

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.