Thanks:  0
Likes:  0

1. 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)

2. 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 ]

3. 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)

4. 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.

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

6. 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)
Under:
DFunctionsWithValidation.zip

Just some friendly forewarning.

7. 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.

8. 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!

10. davidh@ozgrid.com

It's all over my Web site )

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•