CSE Foumulas

Mar 6th, 2002, 08:14 PM

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 · Mar 6th, 2002, 08:19 PM

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 ]

Mar 6th, 2002, 08:24 PM

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 · Mar 6th, 2002, 08:39 PM

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.

Mar 6th, 2002, 09:44 PM

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

Dave Hawley · Mar 6th, 2002, 10:38 PM

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.

Mar 6th, 2002, 11:09 PM

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 · Mar 6th, 2002, 11:18 PM

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!

Mar 6th, 2002, 11:22 PM

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

Dave Hawley · Mar 6th, 2002, 11:23 PM

davidh@ozgrid.com

It's all over my Web site

)

Mar 6th, 2002, 08:39 PM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 08:14 PM	#1
Guest Posts: n/a	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

Mar 6th, 2002, 08:19 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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 ]

Mar 6th, 2002, 08:24 PM	#3
Guest Posts: n/a	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)

Mar 6th, 2002, 09:44 PM	#5
Guest Posts: n/a	Anyone else can help me on this?? Getting quite annoyed with excel now...

Mar 6th, 2002, 10:38 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 6th, 2002, 11:09 PM	#7
Guest Posts: n/a	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.

Mar 6th, 2002, 11:18 PM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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!

Mar 6th, 2002, 11:22 PM	#9
Guest Posts: n/a	Sure Dave, what's your e-mail address?

Mar 6th, 2002, 11:23 PM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	davidh@ozgrid.com It's all over my Web site )