Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column based on criteria from two other columns
In Excel 2003, I have been trying to figure out a way to get this to work,
and I have had no success. I have a .CSV file that outputs claim data from our claims system. I would like to be able to copy and paste this data into a workbook that shows the past four policies' claims information, and have a formula in a given cell find the total value of all claims for a given policy effective date and a particular claim code. I have tried IF, SUMPRODUCT, SUMIF and VLOOKUP formulas, but I can't find a formula that gives me the value I want. When I paste the info from the .CSV file into Sheet 1 of my workbook, Column L contains the Effective Date (which ends up automatically formatting the cells to date format), Column W contains the Claim Code, and Column AH contains the Net Incurred Value. I was able to calculate the total Net Incurred for Claim Code 193 with this formula: =SUMIF(Sheet1!W2:Sheet1!W9999,"=193",Sheet1!AH2:AH 9999) However, what I am really looking for is a way to get only the total of the Net Incurred for Claim Code 193 that also has an Effective Date of 10/19/2006. (I can run claims data for a particular insured, but the number of rows in the .CSV will vary by insured, as each insured has a different number of claims. I go up to row 9999 in the arrays because a particular insured may have hundreds of claims, but none should have 10,000 or more.) I have tried using the following formula to total up the Net Incurred for a 10/19/2006 Effective Date, with the thought that I could add a condition for the Claim Code, but I end up with a #VALUE! error: =IF(TEXT((Sheet1!L2:Sheet1!L9999),"mmddyyyy")=1019 2006,SUM(Sheet1!AH2:Sheet1!AH9999),0) Is there a way for me to get the total Net Incurred for a particular Class Code and Effective Date using Excel 2003? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column based on criteria from two other columns
Try the below with date in cell J1
=SUMPRODUCT((Sheet1!L2:L9999=J1)*(Sheet1!W2:W9999= 193), Sheet1!AH2:AH9999) OR =SUMPRODUCT((TEXT(Sheet1!L2:L9999,"mmddyyyy")="101 92006")* (Sheet1!W2:W9999=193),Sheet1!AH2:AH9999) If 193 is text change it to "193" If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(Sheet1!AH2:AH9999,Sheet1!L2:L9999,date,She et1!W2:W9999,193) If this post helps click Yes --------------- Jacob Skaria "glinishmak" wrote: In Excel 2003, I have been trying to figure out a way to get this to work, and I have had no success. I have a .CSV file that outputs claim data from our claims system. I would like to be able to copy and paste this data into a workbook that shows the past four policies' claims information, and have a formula in a given cell find the total value of all claims for a given policy effective date and a particular claim code. I have tried IF, SUMPRODUCT, SUMIF and VLOOKUP formulas, but I can't find a formula that gives me the value I want. When I paste the info from the .CSV file into Sheet 1 of my workbook, Column L contains the Effective Date (which ends up automatically formatting the cells to date format), Column W contains the Claim Code, and Column AH contains the Net Incurred Value. I was able to calculate the total Net Incurred for Claim Code 193 with this formula: =SUMIF(Sheet1!W2:Sheet1!W9999,"=193",Sheet1!AH2:AH 9999) However, what I am really looking for is a way to get only the total of the Net Incurred for Claim Code 193 that also has an Effective Date of 10/19/2006. (I can run claims data for a particular insured, but the number of rows in the .CSV will vary by insured, as each insured has a different number of claims. I go up to row 9999 in the arrays because a particular insured may have hundreds of claims, but none should have 10,000 or more.) I have tried using the following formula to total up the Net Incurred for a 10/19/2006 Effective Date, with the thought that I could add a condition for the Claim Code, but I end up with a #VALUE! error: =IF(TEXT((Sheet1!L2:Sheet1!L9999),"mmddyyyy")=1019 2006,SUM(Sheet1!AH2:Sheet1!AH9999),0) Is there a way for me to get the total Net Incurred for a particular Class Code and Effective Date using Excel 2003? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing one column based on criteria from two other columns
Try this...
Use cells to hold the criteria: A1 = date of interest like 10/19/2006 B1 = Claim Code like 193 Then: =SUMPRODUCT(--(Sheet1!L2:L9999=A1),--(Sheet1!W2:W9999=B1),Sheet1!AH2:AH9999) -- Biff Microsoft Excel MVP "glinishmak" wrote in message ... In Excel 2003, I have been trying to figure out a way to get this to work, and I have had no success. I have a .CSV file that outputs claim data from our claims system. I would like to be able to copy and paste this data into a workbook that shows the past four policies' claims information, and have a formula in a given cell find the total value of all claims for a given policy effective date and a particular claim code. I have tried IF, SUMPRODUCT, SUMIF and VLOOKUP formulas, but I can't find a formula that gives me the value I want. When I paste the info from the .CSV file into Sheet 1 of my workbook, Column L contains the Effective Date (which ends up automatically formatting the cells to date format), Column W contains the Claim Code, and Column AH contains the Net Incurred Value. I was able to calculate the total Net Incurred for Claim Code 193 with this formula: =SUMIF(Sheet1!W2:Sheet1!W9999,"=193",Sheet1!AH2:AH 9999) However, what I am really looking for is a way to get only the total of the Net Incurred for Claim Code 193 that also has an Effective Date of 10/19/2006. (I can run claims data for a particular insured, but the number of rows in the .CSV will vary by insured, as each insured has a different number of claims. I go up to row 9999 in the arrays because a particular insured may have hundreds of claims, but none should have 10,000 or more.) I have tried using the following formula to total up the Net Incurred for a 10/19/2006 Effective Date, with the thought that I could add a condition for the Claim Code, but I end up with a #VALUE! error: =IF(TEXT((Sheet1!L2:Sheet1!L9999),"mmddyyyy")=1019 2006,SUM(Sheet1!AH2:Sheet1!AH9999),0) Is there a way for me to get the total Net Incurred for a particular Class Code and Effective Date using Excel 2003? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells based on mulitiple criteria over mulitple columns | Excel Discussion (Misc queries) | |||
Summing/Counting Data in One Column Based on Criteria Another Col | Excel Worksheet Functions | |||
Summing specific Columns based on Criteria | Excel Discussion (Misc queries) | |||
Summing one column where criteria in two other columns are met | Excel Worksheet Functions | |||
Counting & Summing based on criteria on another column | Excel Discussion (Misc queries) |