Is it possible to count several ranges of cells, where for one of those ranges I need to count repeating cells only once?

ziphem

New Member
I wasn't quite sure how to phrase this question, hopefully the title makes some sense. I am completely stumped on this, and would greatly appreciate any guidance!!

I need to count a range of cells (read: columns) based on their values. Ok, no problem there. However, for one of the columns, I am supposed to count unique cell values - meaning, if the cell value repeats itself in several cells (and falls in the range of cells I'm counting), it should only be counted once.

Thus, say I'm counting 100 rows

Column A=Customer ID (e.g., F1023)
Column B=Number of (unique) visits (e.g., 1/2/2012)
Column D=Rep (e.g., John)

Column A Column B Column D Column G
F1023 1/2/2012 John 1/1/2012
F1023 1/3/2012 John 2/1/2012
F3312 1/4/2015 John
F3321 1/5/2012 Mary
F1023 1/6/2012 John
C4450 1/18/2012 Mary
B3320 1/21/2012 John
B3320 1/22/2012 John
B3320 2/5/2012 John
B3320 1/2/2012 John
Veee3 3/3/2012 John
Veee3 1/12/2012 Henry
Veee2 2/1/2012 John
Veee5 1/12/2012 Mark
Ve4 2/12/2012 John

To count the number of visits rep JOHN (Column D) has made to customers (Column B) in January, I can use:

=SUMPRODUCT((\$D\$1:\$D\$20="John")*(\$B\$1:\$B\$20>=\$G\$1)*(\$B\$1:\$B\$20<\$G\$2))

To count the number of customers (Column A) that all sales reps (Column D) have visited , I can use:

=SUMPRODUCT((\$A\$1:\$A\$20<>"")/COUNTIF(\$A\$1:\$A\$20,\$A\$1:\$A\$20&""))

But how do I combine both of those? How do I count the number of customers (Column A) that John (Column D) has visited during January (Column B & G)??

Thank you so much!!

Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Replies
3
Views
139
Replies
6
Views
276
Replies
15
Views
444
Replies
5
Views
143
Replies
10
Views
344

1,207,436
Messages
6,078,551
Members
446,347
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back