I need to get the total sum of the day's volume by ID, but for the life of me I cannot seem to find a formula that will work. Can someone please help me? I've been trying to find/create a formula to make this work for the last week, and I just keep coming up empty. Any help would be greatly appreciated!!!
Total Daily Volume.PNGClinic Data.PNG
Last edited by FaithH; 04-14-2014 at 03:59 PM.
Hi, and welcome to the forum.
Have you tried a SUMIF() function?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard! Thank you!
I have tried the SUMIF function, but I need it to sum based on the ID, which I can't seem to do. I'm not sure if there's a MATCH function, or if I need to use a SUMPRODUCT. When I've used SUMIF in this before, it hasn't calculated properly.
Hi,
I see no reason why a SUMIF wouldn't work. Would you upload your workbook please. We prefer these to pictures as explained in the guidance area of the rules. Not many of us are prepared to recreate your workbook when you have it available.
Attached! Thank you!
Excel Help-041414.xlsx
Which is the data you are trying to Sum and where? Can you offer a few examples of results that you expect.
Sure!
So I want the total daily sum by Xfin ID from "Clinic Data" sheet in the Total Daily Volume sheet. For example (I'll use Loc002475 - line 21 on Clinic Data), I want the sum of all 7 columns (CY, GX, OF, PA, RF, SA, UC) for 4/2 to be summed together for Loc002475 (line 17 on Raw Data) on Total Daily Volume-Raw Data tab for 4/2 - that total would be 8 (for 4/4 it would be 10).
Then for Apr 14 tab, I need the total Genetics (GX) for the day by Sales Group (column B on Clinic Data - House is NAO also), as well as total Tox (sum of OF, RF, SA, UC) for the day by Sales Group (again, column B on Clinic Data, and House being included in NAO total).
Does that help? I know it's crazy... this was created long before I came along and truly needs to be simplified!
Hi,
See attached.
I've added formulae in columns C, J, Q of the Clinic Data sheet. Not sure how you can use columns D:I. K:P etc since the values in row 2 are not present on the Raw Data sheet.
I don't understand the second requirement for the Apr 14 sheet. Can you give some specific examples.
All that said, if you have any control over how the data is collected I'd urge you to avoid the multi columns on the Raw Data sheet and just have columns A & B plus a Date column C and a number column D. and enter data (or download it) for a date only when it is relevant. Most of the numbers are zero and irrelevant.
If you arrange your data in that way you'll be able to analyse it far more effciently and could use a Pivot table.
Hi, thanks Richard! I actually need the totals from the day in Clinic to go in the Total Daily Volume tab. I put the daily number in the Clinic Data tab - I want those numbers summed in the Raw Data tab.
For the Apr 14 tab, it's just a sum of the Total numbers (from the Clinic Data tab). Genetics is GX (cell D2) and Toxicology is the sum of OF (E2), RF (G2), SA (H2), & UC (I2). So on 4/1 if you filter column B (Sales Team) for just NAO & House you'll see they had a total of 62 GX (Genetics), and a total of 861 OF, RF, SA, & UC (Toxicology).
These are 3 separate reports that are sent out daily, and also include volumes from previous months (broken out even more) so I have to include the ones that have zeros because they've had volume within the last 3 months. These are just tabs from the 3 reports that I am trying to link to reduce reporting time. I love Pivot Tablesand I use them every chance I get! However I work for a company that is growing fast, but still running reports as though they haven't grown at all...
Can we rewind a bit. You already have numbers in the Total Daily VolumenRaw Data tab. Are you saying that you want these replaced by formulae?Originally Posted by FaithH
Yes. Those numbers I had to place manually. I'm looking for a way to get in them in through formulas, instead of having to vlookup everyday.
Hi,
In C2 Raw Data copied across & down
Formula:
Please Login or Register to view this content.
In
D3 April 14 copied down
Formula:Please Login or Register to view this content.
E3
Formula:Please Login or Register to view this content.
Copied Down
These Raw Data formula are for Genetics. For Toxicology the D3 equivalent in J3 you'll need to add 5 sets of formulae together, and the Match identified in red will need to add an offset for Clinic Data column E of +1, for column G +3 etc. i.e.
MATCH(C3,'Clinic Data'!$3:$3,FALSE)+1
MATCH(C3,'Clinic Data'!$3:$3,FALSE)+3
Hopefully you can get the general idea from the above. I'm attaching the partially completed workbook
Thanks! Looks great!!!
What does the OFFSET do? I know you can use it to say one row down, 2 rows, etc, but I don't understand how it's being used in this book?
Hi
Any OFFSET function consists of up to 5 elements, the first 3 are mandatory.
The first element is the 'anchor' cell on which the rest of the offset is based.
The next two elements indicate the number of columns and rows to offset by
Having established an offset cell from the anchor, the 4th & 5th elements indicate how wide and high the offset range should be. If these are not specified the offset cell is a single cell.
On the Raw Data tab C2
=IFERROR(SUM(OFFSET('Clinic Data'!$A$1,MATCH($A2,'Clinic Data'!$A:$A,FALSE)-1,MATCH(C$1,'Clinic Data'!$3:$3,FALSE)-1,1,7)),"")
The anchor cell is A1 on the Clinic Data
The MATCH($A2,'Clinic Data'!$A:$A,FALSE) finds the row number (40) in the Clinic data that contains the Loc000877 ID, and since we're using an Offset from A1 then we want to offset by 39 to get to row 40.
The MATCH(C$1,'Clinic Data'!$3:$3,FALSE) finds the column number (3) in the Clinic data that contains the date in C1. And similarly we deduct 1 because we;re using an Offset (as opposed to an INDEX())
The 4th element is 1 which indicates the offset range is 1 row deep and the 5th element 7 indicates there are 7 columns C:I to include in the range being summed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks