sum with two conditions and a wildcard

  • Thread starter Thread starter czamora
  • Start date Start date
C

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
 
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
 
Back
Top