Hello Petercrowe,
Perhaps the best solution for this type of problem is the use of
"array formulas" which repeat a calculation over a range of cells and
can return a single results.
For example, assuming the following layout
Cells A2 through A6 have the names of the fruit as indicated in your question.
Cells B2 through B6 have the numbers as indicated in your question.
Cells D2 through D4 have the names "Apple", "Banana", and "Orange"
Then copy / paste the following function into E2:
=SUM(IF(ISERROR(SEARCH(D2,A$2:A$6)),0,B$2:B$6))
and enter as an array function (CTRL-SHIFT-Enter, or CTRL-SHIFT-click
on the check mark). If entered properly 20 should appear as the result
and the formula bar should show
{=SUM(IF(ISERROR(SEARCH(D2,A$2:A$6)),0,B$2:B$6))}
indicating that it is entered as an array formula (braces added automatically).
You can then select E2 through E4 and fill down to compute the values
for the other types of fruit. The results I got were 20, 160, and 40.
To explain the parts of the formula:
- SEARCH is used to find matching strings - #VALUE is returned if no match
- ISERROR is used to return TRUE if #VALUE is returned, FALSE otherwise
- IF is used to return 0 if ISERROR was TRUE, the count if FALSE
- SUM is used to add the counts (of the matching cells)
Entry as an array formula is needed to cause the formula to be
repeated for each fruit / count pair of values.
See the Excel help titled
About array formulas and how to enter them
(the name may vary slightly by Excel version) for more information on
the capabilities of array formulas in Excel. [or search for array
formula]
Please make a clarification request if you have any difficulty
adapting / entering the formula for your use or if any part of the
answer is unclear.
Good luck with your work.
--Maniac |
