Trying to Find Sum of Unique Values with two Criteria (one is a Match to another column and the other is a Date Range)

JRCEI

New Member
Joined
Mar 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
As you can tell by the title I'm extremely knew to this forum and a bit new in explaining exactly what I'm trying to do. Apologies in advance if I'm killing anyone with incorrect grammar and if I'm overcomplicating something simple. So here we go!

I have a spreadsheet that is split up with 6 different tabs. I'm going to be pasting raw data into the "MDU DATA", "LEAD DATA", "SOLD" tabs and will have my first tab "Dashboard" pull off of those based on a date reference posted on the "Dashboard" tab so so I can just enter a number to shift all the columns to the appropriate dates.

The original formula that I'm working with is this:

1616620739580.png


This works well for just capturing the total amount of line items that reference "CArce" from the Raw Data Sheet(s) based on the date that matches in D1. The sheet itself is set up so that when I change the number in C1 it will shift all of the dates above the columns to display that weeks dates and thus pull data from the Raw Data Tabs that much easier.

My issue is I'm looking to do something more, as we sometimes have doubled data from incorrect input by our field reps. So I'm looking to get a Sum value of all the items in 'MDU DATA'!B:B minus the duplicates, that match with username "CArce" in 'MDU DATA'!I:I with the date that matches the date in 'Dashboard'!D1.

I tried this (below) but it doesn't seem to work like I thought it would and I'm most likely WAY off in regards to what I'm attempting:

=COUNTIFS('MDU DATA'!$I:$I, "CArce", 'MDU DATA'!$H:$H, Dashboard!E1,'MDU DATA'!$B:$B,SUM(--(FREQUENCY(IF('MDU DATA'!B:B<>"",MATCH('MDU DATA'!$B:$B,'MDU DATA'!$B:$B,0)),ROW('MDU DATA'!$B:$B)-ROW('MDU DATA'!$B2)+1)>0)))

Once I have that working, I am working on a formula that does the same thing but within a date range like this:

1616621051744.png


So I'm trying to build a formula that works the same as the previous Dashboard but with the additional criteria of being in-between those dates.

I tried something like this and it's close but I don't think it's right (yes I know it's a different username that picture 2, I'll be doing this for all of them):

=SUM(COUNTIFS('MDU DATA'!$I:$I,"CArce",'MDU DATA'!$H:$H,">="&D$1,'MDU DATA'!$H:$H,"<"&E$1),-SUM((FREQUENCY(IF('MDU DATA'!B:B<>"",MATCH('MDU DATA'!B:B,'MDU DATA'!B:B,0)),ROW('MDU DATA'!B:B)-ROW('MDU DATA'!B2)+1)>0)))

Just for a bit of information as to the headers on the MDU DATA sheet I will paste that here:


TL;DR

1616621318266.png


I am trying to make a formula that count Unique entries in Column B that match with a specified name in Column I and also match with a date referenced from another sheet to Column H.

Then I'm trying to make a formula that does the same thing but rather than matching an exact date I would like it to count between a specified date range. Example:

1616621604858.png

1616621625292.png




Any and all assistance would be GREATLY appreciated!

Sincerely,

JRCEI
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Watch MrExcel Video

Forum statistics

Threads
1,130,336
Messages
5,641,565
Members
417,222
Latest member
Broflovski

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top