Formula to Count Base On Criteria

Originally Posted by JohnTopley

Try

in C8

=IF(COUNTIFS('Data Dump'!$B:$B,$B$7,'Data Dump'!$C:$C,">=" & $B$3,'Data Dump'!$C:$C,"<=" & $C$3,INDEX('Data Dump'!$D:$H,,MATCH($B8,'Data Dump'!$D$1:$H$1,0)),1),1,0)
Copy down for John

Change highlighted range to next mgr.

I suggest a layout with products in column B and Account managers in columns C to Z so you create a ore "formula-friendly" layout

See Sheet1 of attached

Hello John,

Formula works fine if there's one account for the Sold Manager. But with if the Sold manager has multiple account as seen on the new attachment. It would count the other accounts as one within the date range.

Please see new attachment.

thank you for your help is greatly appreciated.

So you want a count by each account for each Account Mgr?

Originally Posted by JohnTopley

So you want a count by each account for each Account Mgr?

Yes it should count each account once by Sold Mgr Name and by product. I hide the sheet1 from your original reply with your formula if you would like I've re-attached with your view. but for example:

for Sold Manager John for Product Notebook within the data range should count 3 since it would count Walmart, RiteAide, and Homedepot once.

Thank you,

Originally Posted by nflsales

Better to Arrange Items Horizontally and Sold Managers Name Vertically to avoid unnecessary confusion

Hello Nflsales,

I've attached the data with the view requested. As i mentioned to JohnTopley it should count each account once by Sold Mgr Name and by product for example:

for Sold Manager John for Product Notebook within the data range should count 3 since it would count Walmart, RiteAide, and Homedepot once.

Thank you nflsales I greatly appreciate your help as well.

Originally Posted by JeteMc

This proposed solution isn't as elegant as I would like, however perhaps it will help until something better comes along.
A helper column is added to the data dump, populated by the formula: =DATE(LEFT(C2,4),RIGHT(C2,2),1)
A Pivot Table is produced as shown on sheet 3.
The second table on the 'Different Data View' sheet is basically populated by the formula: =SUMIFS(Sheet3!C$4:C$11,Sheet3!$A$4:$A$11,$B15)
Instead of dictating the range of dates, B2 and C2 display the range selected in the Pivot Table's Report Filter by referencing the values in H12:I12 of the Pivot Table.
Let us know if you have any questions.

Hello JeteMC,

I greatly appreciate the help I would just need a more dynamic formula since some people who will be viewing this file are verrry novice with excel but it do temporary work for myself so very thankful.

See attached:

Sheet1 has list of Accounts: Named Range called "Accounts"

in "Different View Data"

in in C21

=SUM(IF(COUNTIFS('Data Dump'!$B:$B,$B21,'Data Dump'!$C:$C,">="&Date1,'Data Dump'!$C:$C,"<="&Date2,INDEX('Data Dump'!$D:$H,,MATCH(C$20,'Data Dump'!$D$1:$H$1,0)),1,'Data Dump'!$A:$A,Accounts),1,0))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across and down

the dates in "Table" B3 and C3 are named ranges called "Date1" and "Date2"

Originally Posted by JohnTopley

See attached:

Sheet1 has list of Accounts: Named Range called "Accounts"

in "Different View Data"

in in C21

=SUM(IF(COUNTIFS('Data Dump'!$B:$B,$B21,'Data Dump'!$C:$C,">="&Date1,'Data Dump'!$C:$C,"<="&Date2,INDEX('Data Dump'!$D:$H,,MATCH(C$20,'Data Dump'!$D$1:$H$1,0)),1,'Data Dump'!$A:$A,Accounts),1,0))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across and down

the dates in "Table" B3 and C3 are named ranges called "Date1" and "Date2"

oh my god yes thank you so much this worked beautifully.