| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumif with subtotal
Lights 12,505.00
Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
To give a further idea of what I'm attempting to achieve:
=sumif(c3:c191,"Lights",subtotal(9,d3:d191)) - doesn't work. I use Excel 2007. -- Kind regards, Robert "Robert" wrote: Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Try this:
Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Thanks for your help thus far.
Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Just remove the test for Lights:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12) And, if just want Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks for your help thus far. Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Thanks Biff. Just one more if I may. What if there is numerical data in
adjacent cells (say, F, G and H, etc) that continue to the right? -- Kind regards, Robert "T. Valko" wrote: Just remove the test for Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12) And, if just want Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks for your help thus far. Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
What about it?
How does it relate? -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks Biff. Just one more if I may. What if there is numerical data in adjacent cells (say, F, G and H, etc) that continue to the right? -- Kind regards, Robert "T. Valko" wrote: Just remove the test for Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12) And, if just want Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks for your help thus far. Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Presently we have dealt with data in columns B and C. There is additional
data in columns D and beyond. What I am attempting to find out is what the formula would be for cells D and so on? What part of the Formula changes as it is copied across the spreadsheet? I realise that certain parts of the formula will remian absolute. Thanks. -- Kind regards, Robert "T. Valko" wrote: What about it? How does it relate? -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks Biff. Just one more if I may. What if there is numerical data in adjacent cells (say, F, G and H, etc) that continue to the right? -- Kind regards, Robert "T. Valko" wrote: Just remove the test for Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12) And, if just want Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks for your help thus far. Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using sumif with subtotal
Oh, OK!
If you want to sum column C, D, E, F, etc., just make the reference to column B absolute and the reference to the sum column relative: =SUMPRODUCT(SUBTOTAL(3,OFFSET($B2:$B12,ROW($B2:$B1 2)-ROW($B2),0,1)),($B2:$B12="Lights")+($B2:$B12="Sola r"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Presently we have dealt with data in columns B and C. There is additional data in columns D and beyond. What I am attempting to find out is what the formula would be for cells D and so on? What part of the Formula changes as it is copied across the spreadsheet? I realise that certain parts of the formula will remian absolute. Thanks. -- Kind regards, Robert "T. Valko" wrote: What about it? How does it relate? -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks Biff. Just one more if I may. What if there is numerical data in adjacent cells (say, F, G and H, etc) that continue to the right? -- Kind regards, Robert "T. Valko" wrote: Just remove the test for Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Solar"),C2:C12) And, if just want Lights: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),--(B2:B12="Lights"),C2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Thanks for your help thus far. Now what if I just want solar? How would the formula loo please? -- Kind regards, Robert "T. Valko" wrote: Try this: Lights/Solar in column B Amounts in column C Full unfiltered range is B2:C12 =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B12,ROW(B2:B12)-ROW(B2),0,1)),(B2:B12="Lights")+(B2:B12="Solar"),C 2:C12) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Lights 12,505.00 Solar 23,622.44 Lights 21,771.00 Solar 23,622.44 Lights 23,739.00 Solar 23,622.44 Lights 9,594.00 Solar 23,622.44 Using above data as example, how do I subtotal using sumif where data has been filtered in a larger database? The filtered data base has returned data that has already been correctly subtotalled using function_num 9. This list now contains hidden rows. I now wish to further subtotal the list using criteria lights or solar in the sumif? -- Kind regards, Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif / subtotal / something else? | Excel Discussion (Misc queries) | |||
| Subtotal and sumif help | Excel Discussion (Misc queries) | |||
| subtotal and sumif | Excel Worksheet Functions | |||
| Sumif & subtotal | Excel Discussion (Misc queries) | |||
| Subtotal on SumIf | Excel Worksheet Functions | |||
Linear Mode
