Summing one column based on criteria from two other columns

[glinishmak]

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:AH9999)

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")=10192006,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?

 

[Jacob Skaria]

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")="10192006")*
(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,Sheet1!W2:W9999,193)

If this post helps click Yes

 

[T. Valko]

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)