Countifs with Dynamic data on Google sheet

[asolopreneur]

https://docs.google.com/spreadsheets/d/1u2sRu7MLGYJ0aYQUEnVLED3iR7rCHqW1z0RCJEosmlI/edit?usp=sharing

I have list of sales person who works in various regions. I've fetched number of calls they did on particular date but I don't want to change formulas daily. How can I use offset countif to count how many calls particular sales person did?

How can I use whole sheet as a range to fetch the number of calls?

Can I use array formula to include whole sheet as a reference?

Please click on Google sheet link you can edit as you want.

 

[mrhstn]

This should do it...

=counta(filter('Regions & Calls'!$D:$D,'Regions & Calls'!$D:$D=B$1, 'Regions & Calls'!$E:$E=$A2))

I've put this on range B9 down on your sheet.

 

[asolopreneur]

Why It's showing 1 when I drag the formula.

This should do it...

=counta(filter('Regions & Calls'!$D:$D,'Regions & Calls'!$D:$D=B$1,'Regions & Calls'!$E:$E=$A2))

I've put this on range B9 down on your sheet.

 

[mrhstn]

Odd, try this

=countifs('Regions & Calls'!$F$2:$F, B$1, 'Regions & Calls'!$G$2:$G, $A2)

You don't need to define the end of a range in google sheets.

 

[asolopreneur]

The "Regions & Calls" Data will be dynamic, it will change daily. So I don't want to edit formulas on
"Salesman" Sheet.

I want to create a dashboard where I can see number of calls done by sales persons. I also want to know how many days ago the calls were made.

Thanks

Odd, try this

=countifs('Regions & Calls'!$F$2:$F,B$1,'Regions & Calls'!$G$2:$G,$A2)

You don't need to define the end of a range in google sheets.

 

[mrhstn]

You've changed the layout of the original data.

 

[mrhstn]

Try

=SUMPRODUCT((MOD(COLUMN('Regions & Calls'!$D:$K), 2)=0)*('Regions & Calls'!$D:$K=B$1)*(MOD(COLUMN('Regions & Calls'!$E:$L), 2)=1)*('Regions & Calls'!$E:$L=$A2))

D should be the first column of dates, change the K column reference to cover you as far as you will go
E should be the first column of names, careful when changing this as it must finish one column to the right of the date range end (i.e D:K E:L, D:Z E:AA etc)

 

[Aladin Akyurek]

Try

=SUMPRODUCT((MOD(COLUMN('Regions & Calls'!$D:$K),2)=0)*('Regions & Calls'!$D:$K=B$1)*(MOD(COLUMN('Regions & Calls'!$E:$L),2)=1)*('Regions & Calls'!$E:$L=$A2))

D should be the first column of dates, change the K column reference to cover you as far as you will go
E should be the first column of names, careful when changing this as it must finish one column to the right of the date range end (i.e D:K E:L, D:Z E:AA etc)

1. You need to robustify this GS (Google Sheets) formula...

=SUMPRODUCT((MOD(COLUMN('Regions & Calls'!$D:$K)-COLUMN('Regions & Calls'!$D:$D), 2)=0)*('Regions & Calls'!$D:$K=B$1)*(MOD(COLUMN('Regions & Calls'!$E:$L)-COLUMN('Regions & Calls'!$E:$E), 2)=0)*('Regions & Calls'!$E:$L=$A2))

This can be shortened to just:

=SUMPRODUCT((MOD(COLUMN('Regions & Calls'!$D:$K)-COLUMN('Regions & Calls'!$D:$D), 2)=0)*('Regions & Calls'!$D:$K=B$1)*('Regions & Calls'!$E:$L=$A2))​

​

2. It looks like the Google Sheets performance is way superior to the MS Excel performance with whole column references.

​

 

[mrhstn]

Thank you Aladin

The performance difference may be down to the default rows, Google Sheets will open with just 1000 rows. The app specifications mean a basic sheet can't be as large as an Excel sheet, although there should be plenty of space for average use.

Incidentally you can exclude parts of a full column reference in GSheets, A20:A is a perfectly valid range to put in a formula.