Dget, sumif vlookup

[Guest]

Hi,

I am in the process of switching from Lotus to Excel.

I use a lot of dget and dsum functions in Lotus.
However it looks like in excel instead of being
able to include multiple criteria in the formula
you need to have separate criteria ranges.

Is there an easier way to accomplish this?

Sample data:

PLAN_ID PLAN_NAME 0/2 205/10 205/20
123555 plan 2 346 3680
123522 plan2 3 123 555

want if plan_id=123522 and 0/2 = 3 and column header is 205/10 then 123

Also don't want to have to count columns, want to be able to give headings.

Thanks in advance for any help.

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[JulieD]

Hi Adella

SUMPRODUCT might give you what you're after if you want to SUM the value in
'205/10
=SUMPRODUCT(--(A2:A22=123522),--(B2:B22="plan2"),(D222))

or COUNT the number of times 123522 & plan2 are in their respective columns
together
=SUMPRODUCT(--(A20:A22=123522),--(B20:B22="plan2"))

Cheers
JulieD

 

[Myrna Larson]

I don't know what you'll find at the site Frank mentions, but if you want to
"give headings" in the ultimate array or SUMPRODUCT formula, you must set up
named ranges that use those headings. If the headings are as you show, the
last 3 can't be used as range names: a slash isn't a legal character in a name
because it's the division operator.

You may want to look at Pivot Tables. Perhaps they will allow you to eliminate
your D formulas.

I once tried a 3rd-party addin from an outfit called Spinnaker. It had custom
formulas to mimic the Lotus functions you're talking about. But I found that
it didn't work reliably, particularly when modifying formulas on an existing
worksheet. Seemed to fare better when I was creating a new sheet "from
scratch". Several years ago I tried to work with the developer to find and fix
the problem, but we didn't succeed. Maybe it's fixed now. Here's the link to a
place where you can download it. I see he wants $90 for it, but there's a
trial period to see if it works for you.

http://www.totalshareware.com/asp/detail_view.asp?application=33222

 

[Guest]

Thanks for all your help. The formula worked great, however
is there a way to just say what columber number and not have
to put the letter in? The reason I ask is because I can use
the match function to tell me what headin g is in what column so if the
columns change places with different databases it doesn't give me incorrect
results.

This is the vlookup I'm using but would like to also be able to sum:

=VLOOKUP(A6,castdata,(MATCH(C10,cost!A1:FZ1,0)),FALSE)

Thanks again for all your help.

Adella