-1

I have a massive dataset and am preparing a dashboard based on this dataset.

On my dashboard, I have a drop-down menu that allows me to select a month of my choice, from Jan to Apr.

Visitor Jan Feb Mar Apr
Jenny   2   3   0   1  
Peter   2   0   1   3  
Charley 0   2       4  
Charley 1   2   2   3  
Sam     1   4   2   3  
Peter   2   2   5   0  
John    3   3   6   9  
Robin   4   0   7   0

I am looking for a formula that will give me the number of unique visitors who have been active at least once in the month that I choose from the drop-down menu.

Hoping this is really clear, but if not, please feel free to shoot back your questions.

pnuts
  • 58,317
  • 11
  • 87
  • 139
NEJ
  • 1
  • 1

1 Answers1

0

This may be easier with Excel 2013, but if the results you want from your example are 6, 5, 5, and 5 for Jan>April respectively then perhaps:

  1. Create a PivotTable from multiple consolidation ranges (example how here and for VALUES choose Sum of Value.

  2. Count the non-zero values in the PT by column with a formula such as: 

    =COUNTIF(H5:H10,">"&0)

The above however would not be convenient for repetition each month, though a whole year might be prepared at one time.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139