CSE Foumulas

[Guest]

Hi,
Am pretty new with excel, so just bear with me for just a second. Read Mr Excel's CSE examples, guess I am too dumb to make any sense out of it. Can someine help me with this?

Sheet 1

Value Code
--------------


Sheet 2
= Number of (Value = 25 and Code = B)

Help anyone? Thanks in advance

 

[Anne Troy]

Do you want to only sum the Bs if the value is 25, or do you want to sum all of the Bs?

If all values, I just created a similar function:

=SUMIF(Sheet1!$B:$B,"B",Sheet1!$C:$C)

_________________
TheWordExpert
This message was edited by Dreamboat on 2002-03-06 19:20

 

[Guest]

sum the Bs if the value is 25.. aa.. that's the better way to phrase this...

Also, do you guys think its feasible to control another vba enabled software using word/excel. My boss has this 'bright' idea about using excel's vba as a front end to access our mainframe server using attachmate extra! (a vba enabled emulation software)

 

[Anne Troy]

Check out Sumif with multiple criteria here:

http://www.cpearson.com/excel/array.htm


And, no. I don't know anything about that. You might want to ask it as a new Question tho.

 

[Guest]

Anyone else can help me on this?? Getting quite annoyed with excel now...

 

[Dave Hawley]

Hi

I am happy to help! but before I do and you say you are new be aware the array formulas (CSE) are very bad for excels re-calculations. I get many requests from clients that they can no longer use their Workbooks and 9 times out of 10 it's because of array formulas.

A far better option in most case are the Dfunctions and Excel has some good examples of these, just type "Database Functions" into Excel's help. I also have some eaxmples here (though slightly more advanced use)
http://www.ozgrid.com/download/default.htm
Under:
DFunctionsWithValidation.zip

Just some friendly forewarning.

 

[Guest]

Thanks.. I wanted to avoid using d functions because the criteria range would be quite difficult to specify... I'll let you know the whole situation then. I work in a collection agency. Our collectors are given commission depending on the amount and situation.

I maintain a table for each of the collectors. Because of a new commission structure, I had to rewrite all my previous formulas (the easy ones)

Account #
Customer Name
Bucket
Claimable
Code
PushBack
SV Code
SV Name
Collect Amount

The fields of interest are the Code and Claimable.

Now, I need to generate a report that look something like
(A) (B) (C) (D)
Claimable Code Quantity Total
$25 B ((need help)) A * C
$25 NB ((need help)) A * C


And from (D) we can calculate more incentive for the site visit (collector) guys. Hope this is detailed enough. Thanks for your help! Appreciate it.

 

[Dave Hawley]

If you send me the Spreadsheet I'd be happy to get you off to a start. Just hate it when so called experienced users suggest array formulas, they are a slippery slope to nowhere!

 

[Guest]

Sure Dave, what's your e-mail address?

 

[Dave Hawley]

davidh@ozgrid.com

It's all over my Web site )