Sumif from another worksheet

[Savaal]

I have searched feverishly for the answer, but can't seem to figure this issue out. I have a worksheet with data for sales for our company. I need to total only the subtotals in column E, which crystal reports puts in the same column as the line items that are sold. I want to parse out the bolded and underlined items in a column and put the total on another worksheet, presumably cell A2.

I have tried many variations on this using native functions in excel, and now have looked into writing a vba script to do this, but can't seem to get a handle on the complication. I am not even sure where I should put the script, in Sheet2, or in workbook. Anyway, please help!!!

Here is the formula I have tried recently.

This code in sheet2, cell A2 doesn't seem to work. Since there is data in row G that specifies quantity, I figured I would try it this way, since the data is numeric, I would do a check against the quantity field instead, but it only totals the first one in the list. There are spaces in the list, and I worry that is messing this up.

=DSUM(Sheet1!E:G,Sheet1!E:E,Sheet1!G:G>0)

I have also tried to do a sum based on the IsNumber and isNonText functions, but still get no luck. Thanks everyone!!!

S

 

[Aladin Akyurek]

Care to post a 10-row sample along with the desired result?

 

[Savaal]

A B
01 Invoiced $ Shipped Date
02 285.50 05/18/2011
03 285.50 Part Total
04
05 185.90 05/18/2011
06 185.90 Part Total
07 291.90 05/18/2011
08 291.90 Part Total
09
10 519.75 05/06/2011
11 519.75 05/04/2011
12 1,039.50 Part Total



On Sheet2, A2, I want $1802.80, which is the total of A3, 6, 8, and 12

S

 

[Aladin Akyurek]

A B
01 Invoiced $ Shipped Date
02 285.50 05/18/2011
03 285.50 Part Total
04
05 185.90 05/18/2011
06 185.90 Part Total
07 291.90 05/18/2011
08 291.90 Part Total
09
10 519.75 05/06/2011
11 519.75 05/04/2011
12 1,039.50 Part Total



On Sheet2, A2, I want $1802.80, which is the total of A3, 6, 8, and 12

S

=SUMIF($B$2:$B$12,"Part Total",$A$2:$A$12)

Prefix the sheet name if needed.

 

[Savaal]

Thanks. I don't know why you need the dollar sign, but it works. Would you be willing to tell me why those are needed? Thanks again!

S

 

[T. Valko]

Thanks. I don't know why you need the dollar sign, but it works. Would you be willing to tell me why those are needed? Thanks again!

S

They're only needed if you're copying the formula to other locations.

The dollar signs $ "lock" the row and/or column ranges so that they won't change when copying the formula.

Look in Excel help for "About cell and range references".

 

[Savaal]

Thanks so much. I appreciate it!

 

[Aladin Akyurek]

Thanks.

You are welcome. Thanks for providing feedback.

I don't know why you need the dollar sign, but it works. Would you be willing to tell me why those are needed? Thanks again!

S

They can be dismissed if you do not need to copy the formula.

 

[T. Valko]

Thanks so much. I appreciate it!

You're welcome. Thanks for the feedback!