summing with multiple criteria -- but with many OR criteria

[Mike C]

Hello, I am wondering if it is possible to use the sumproduct or sumif
function (or some other function, or perhaps the use of array
formulas, or some other trick) in order to do the following.

For example, for:

Column A ColumnB
apple 10
orange 12
tomato 23
blueberry 18
etc etc

How can I say to excel: "sum corresponding data in column B, If column
A has: 'apple' or 'orange' or any of about 15 other items." In
essence, I need to do an OR query with many potential matches. Is
there a way to do this? Can I refer to a range of items within the
Sumif or sumproduct or vlookup formula?

I originally tried using a long sumproduct formula, but it got to be
incredibly long, and didn't seem to work....

Note that this is a formula that I will be using over and over to
extract data from a report, based on the medical service categories of
numerous states, and grouped under numerous dates.

FYI, the end result will look something like this for each State, as
will be pulled from a daily (dynamic) report:

Day 1 Day 2 Etc......
Medical Formula Formula
Surgical Formula Formula
Neonatal Formula Formula
Etc

Thanks for any thoughts.....

 

[T. Valko]

Try this:

List your criteria in a range of cells:

F1 = apple
F2 = orange

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,F1:F2,0))),B1:B4)

 

[Eli]

Hello Mike,
What you are describing is a typical application that people try to do in
Excel but should be done with other tools.

You have a dynamic Excel file which grows on a daily basis and you need to
get aggregated reports and analysis based on criteria which are also
changing.

What you really need is database functionality over your Excel file.

To make your life easier, you could use Business Intelligence tool that will
use the Excel data as its input and will relieve you from the tedious and
repetitive work of updating ranges and copying formulas to newly added rows
or columns.

Such a tool is Prism (www.sisense.com) which handles the Excel files as a
database, thus enables you the creation of complex views as well as visual
Dashboards just by drag-n-drop operations. This will not only improve the
handling efficiency of your Excel files but will open new insights into your
data.



Regards

Eli

 

[Herbert Seidenberg]

Six more ways:
http://www.freefilehosting.net/download/3cica

 

[T. Valko]

LOL!

--
Biff
Microsoft Excel MVP

Hello Mike,
What you are describing is a typical application that people try to do in
Excel but should be done with other tools.

You have a dynamic Excel file which grows on a daily basis and you need to
get aggregated reports and analysis based on criteria which are also
changing.

What you really need is database functionality over your Excel file.

To make your life easier, you could use Business Intelligence tool that
will use the Excel data as its input and will relieve you from the tedious
and repetitive work of updating ranges and copying formulas to newly added
rows or columns.

Such a tool is Prism (www.sisense.com) which handles the Excel files as a
database, thus enables you the creation of complex views as well as visual
Dashboards just by drag-n-drop operations. This will not only improve the
handling efficiency of your Excel files but will open new insights into
your data.



Regards

Eli

 

[Don Guillett]

Agreed