Forum Discussion

ellenjpickett's avatar
ellenjpickett
Copper Contributor
Dec 30, 2017

Counting zeros whilst cross checking number of times they happened

Hi guys

 

Help please!

 

I am trying to establish lead times for my hotel website bookings. I have exported data out of Google Analytics into excel that tells me the date someone searched for a booking (Col C) and what date the hotel stay was for (Col F). These are all laid out in rows (I have ~5000 rows). I have Column L which subtracts the search date from the stay date (i.e. the lead time).

 

Using this I am able to do a COUNTIF to see how many 0 days, 1 day, 2 days, 3 days (etc etc) there were in total from Column L so I can put it all in a nice line graph.

 

However, some of the searches happened more than once. E.g. there are rows that the lead time says 0 BUT that particular search happened 20 times (see Col D). So my COUNTIF formula needs to count not 1 but 20 x 0's for that cell. 

 

How do I do this using a formula? Does that make any sense?

 

Thanks!!

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Use SUMIF where you SUM column D? Or create a pivottable where you use col D as the sum column and the days column as a row field.

Resources