Professional Excel Development 2nd Edition

[isoke81]

I just stated to learn to use sumproduct just today. Senarios are as follows: -

A B C

1 40 01/01/2009 E01/04/2009
2 50 04/01/2009 E21/04/2009
3 60 28/02/2009 E30/04/2009
4 A0009 A0009 A0009
5 0 0 01/01/2009
6 0 0 04/01/2009
7 0 0 28/02/2009


The prefix E denotes emergency cases on 1st April 2009 and so forth

For senario A, using sumproduct =SUMPRODUCT(--ISNUMBER($A$1:$A$4),$A$1:$A$4) i would be getting an answer of 150 ignoring the alphanumerical on row 4

For senario B, i tried to sum the total days in January (which is 2) but i got a #VALUE based on the following formula: =SUMPRODUCT(--ISNUMBER($A$1:$A$4)*(MONTH($A$1:$A$4)=1))

Extending to senario C, I am totally stumped on how to identify E and "04" as the identifier to sumproduct this. The figure I am looking at is 3.

 

[Greg Truby]

isoke,

Welcome to MrExcel. It looks to me like you have a legitimate Excel question here. If that's the case, then you probably want to go ahead and ask your question in the main questions forum. This thread is located in the lounge which is not really focused on problem solving so much as just having fun. [Though, of course, most of us consider solving Excel problems to *be fun*.] :wink:

 

[isoke81]

Oh okay. I created a new thread on the above issue under "Sumproduct Question". Thanks!

 

[xenou]

I read the first chapter of PED last night and feel smarter already! In some aspects, I'm a textbook example of the stages of development - start out using macros, learn a little code, develop some awkward applications that don't scale very well and are hard to maintain - then... ?? I've got one behemoth that frankly works flawlessly and in some ways rather cleverly - but is completely inappropriate for anyone else to use. Although it was only intended to be an intermediate patch (now, two years later still patching it - and worried about what might happen if I weren't here to keep it going). I think I'd have an instant consulting business if I left my job today. I'll look forward to another juicy chapter tonight!

 

[QuietRiot]

I read the first chapter of PED last night and feel smarter already! In some aspects, I'm a textbook example of the stages of development - start out using macros, learn a little code, develop some awkward applications that don't scale very well and are hard to maintain - then... ?? I've got one behemoth that frankly works flawlessly and in some ways rather cleverly - but is completely inappropriate for anyone else to use. Although it was only intended to be an intermediate patch (now, two years later still patching it - and worried about what might happen if I weren't here to keep it going). I think I'd have an instant consulting business if I left my job today. I'll look forward to another juicy chapter tonight!

Does this mean it's already out? I got an email from Barnes & Noble saying that they have packed my order and even got a UPS tracking. So I'm thinking I will have it Monday/Tuesday but I didn't think the book was released yet.

 

[xenou]

Ah, oops, no ... its not the new one ... I ordered a used copy of the current version but at least the price was right

 

[Greg Truby]

¡¡Well zippety doo dah day!! This bit o' cheer was in my inbox when I got home tonight.


Greetings from Amazon.com.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
We thought you'd like to know that we shipped your items, and that this completes your order...
<o></o>
The following items have been shipped to you by Amazon.com:
--------------------------------------------------------------------
Qty Item Price Shipped Subtotal
<o></o>
---------------------------------------------------------------------
<o></o>
Amazon.com items (Sold by Amazon.com, LLC):
<o></o>
1 Professional Excel Develop... $37.79 1 $37.79
<o></o>
Shipped via UPS
<o></o>
...
(estimated delivery date: May 18,2009).

Pretty safe bet this is the first time I've been looking forward to a Monday in a good while.

 

[QuietRiot]

¡¡Well zippety doo dah day!! This bit o' cheer was in my inbox when I got home tonight.


Greetings from Amazon.com.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
We thought you'd like to know that we shipped your items, and that this completes your order...
<o></o>
The following items have been shipped to you by Amazon.com:
--------------------------------------------------------------------
Qty Item Price Shipped Subtotal
<o></o>
---------------------------------------------------------------------
<o></o>
Amazon.com items (Sold by Amazon.com, LLC):
<o></o>
1 Professional Excel Develop... $37.79 1 $37.79
<o></o>
Shipped via UPS
<o></o>
...
(estimated delivery date: May 18,2009).

Pretty safe bet this is the first time I've been looking forward to a Monday in a good while.

I get mine on Tuesday the 19th. Amazon is cheap though only 37.79 eh? You paid basically the same has me and I used a Barnes & Noble gift card of 25.00. They are selling it for 59.99

 

[Greg Truby]

I get mine on Tuesday the 19th. Amazon is cheap though only 37.79 eh? You paid basically the same has me and I used a Barnes & Noble gift card of 25.00. They are selling it for 59.99

Oh, bummer about B & N charging full list. I'm gettin' excited.

Package Progress
Location Date Local Time Description
KANSAS CITY, KS, US 05/18/2009 7:57 A.M. OUT FOR DELIVERY
..................... 05/18/2009 4:18 A.M. ARRIVAL SCAN
LENEXA,KS, US 05/18/2009 3:44 A.M. DEPARTURE SCAN
LENEXA,KS, US 05/16/2009 4:10 A.M. ARRIVAL SCAN
TULSA,OK, US 05/15/2009..9:28 P.M. DEPARTURE SCAN
........................... 05/15/2009 8:17 P.M. ORIGIN SCAN
US 05/15/2009 6:32 P.M. BILLING INFORMATION RECEIVED

Alas, Monday nights are chock full o' stuff with the kids (piano lessons, then soccer, then Boy Scouts) so I won't be able to really dig in until about 9:00 tonight. Feel like a kid waiting for Christmas morning!

 

[texasalynn]

oh shucks - I don't get mine until Thursday.