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:
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:
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
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:
Any and all assistance would be GREATLY appreciated!
Sincerely,
JRCEI
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:
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:
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
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:
Any and all assistance would be GREATLY appreciated!
Sincerely,
JRCEI
