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?

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?

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?