Finding All Tornado Outbreaks on a Particular Day along with Stats for that Event

[boynejs]

I am working on a large tornado spreadsheet. I need to grab all of the occurrences of tornadoes on a particular calendar day. In my example spreadsheet, I chose August 1. from the following equation used in cell n3

=COUNTIFS(A4:A20,K3,B4:B20,L3)

I know there are 4 occurrences. I used the following equation to count down the number of occurrences (I plan on hiding these by painting the text white) by using the following equation...

=IF(SUM(J9,-1)>0,SUM(J9,-1)," ")

I then reference the chosen month (k3) and date (l3) to list by the occurrences. I thought that by using

=IF(SUM(J9,-1)>0,SUM(J9,-1)," ")

I could set the cells in column K and column L to blank if the adjoining cell in column J is blank, but it just keeps listing my month and day even though the cell is blank.

For column M (year), I am getting #VALUE if the occurrence column is blank.

For columns N, O, and P, I get #N/A if the occurrence column is blank.

I would appreciate any help with this.

Jeff B

[Bo_Ry]

Please try at
J9
=TEXT($N$3+1-ROWS(J$9:J9),"0;;")

K9:L9
=IF($J9="","",K$3)

M9
=IF(J9="","",AGGREGATE(15,6,$C$4:$C$20/($A$4:$A$20=K9)/($B$4:$B$20=L9),ROWS(M$9:M9)))

N9:P9
=IF($J9="","",SUMIFS(D$4:D$20,$A$4:$A$20,$K9,$B$4:$B$20,$L9,$C$4:$C$20,$M9))

[josephteh]

Use PivotTable. It is so much more efficient compare to using formulas.

[boynejs]

Thanks so much for the help! It is much appreciated.

Jeff B

[boynejs]

Thanks so much for the help! I am not the greatest when it comes to pivot tables, so that is why I did not use it. I really need to learn more about them.

Jeff B