sum with two conditions and a wildcard

[czamora]

Someone could help me,

I want to use a function that calculate something like a
SUMIF with two conditions.

Somebody says that I could use the SUMPRODUCT worksheet
function, but I have a problem with this function because
I need to use the wildcard "*" to find more occurrences,
as the same way as I use it on the SUMIF function.

Example: I have a lot of accounts and I need to calculate
all the acounts which has the enable column on TRUE and
the account column starts with "acc01"

Account # Enable Balance
acc0101, USA TRUE 500
acc0102, UK TRUE 2000
acc0201, FR FALSE 223.5
acc0222, USA FALSE 12000
acc0103, FR FALSE 250.36

Expected output: 2500

Thanks in advance

 

[Mark Bigelow]

Your best bet is probably an array formula. It may be cumbersome with
more than 20,000 lines of data, but you should be fine. Assuming the
column heading of the first column is in cell A1, this is the formula:

=SUM(IF(B2:B6=1, IF(LEFT(A2:A6, 5)="acc01", C2:C6, 0), 0))

Please let me know if that doesn't work.

Mark
---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!