Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum with two conditions and a wildcard
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum with two conditions and a wildcard
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum with multiple conditions and wildcard | Excel Discussion (Misc queries) | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
How do I count two conditions using a wildcard? | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |