wildcards, SUMIF, and arrays
Posted by SJC on November 21, 2000 4:56 PM
Does anyone know how to create a formula to count the number of records if the records contain certain values? I have a small but growing spreadsheet that contains 3 columns. Column 1 lists products ("A", "B", "C", "A & B", "C& B", etc.). Column 2 lists names of Sales Rep. Column 3 lists sales volume. How do I get the total sales volume for product A (including the combos such as "A & B")? Unfortunately, the product type is growing so I can't sum up sales for A only and sales for A & B and then add the two. Thanks in advance for your help!
Posted by Tim Francis-Wright on November 21, 2000 7:43 PM
Assuming that columns A through C have only the
data you describe (so these formulas are in some
other column), you can use this:
=SUMIF(A:A,"*A*",C:C)
to get all of the A units (A, A & C, AC/DC, etc.)
HTH
Posted by SJC on November 22, 2000 12:20 PM
Thanks Tim. Would this method work in arrays? By this I mean what if we want to sum up sales for each product by each sales rep?
Posted by Tim Francis-Wright on November 22, 2000 2:00 PM
Not really. For one thing, the A:A and C:C
syntax doesn't work with array formulas--Excel
demands specified ranges. For another, as far
as I can tell, array formulas don't handle wildcards
(unlike SUMIF).
However, the following would work (use control-shift-enter)
=SUM((NOT(ISERROR(SEARCH("A",$A1:$A4000))))*($B1:$B4000="Mr. Excel")*($C1:$C4000)*1)
This will get all of Mr. Excel's sales including
product A.
Good luck!
