Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with an excel formula
I have a really tough question that I hope someone can help me with.
Basically, I need to sum up data from three worksheets, but only if a certain condition is met. For example, In 3 worksheets, I have data in B1, B2, B3. A1, A2 and A3 have 3 names in them in each worksheet. Now, I know how to take the sum of each worksheet, but I only want to take the sum of everything excluding cell B2 which is Bill in the example below. The data below appears exactly like this in all three worksheets. sheet1 A1 A2 A3 john bill John B1 B2 B3 12 45 13 sheet2 A1 A2 A3 john bill John B1 B2 B3 12 45 13 sheet3 A1 A2 A3 john bill John B1 B2 B3 12 45 13 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with an excel formula
I'm assuming that I should not take your example literally! If I did then
you could do this: =SUM(Sheet1:Sheet3!B1)+SUM(Sheet1:Sheet3!B3) Otherwise, try this: =SUMPRODUCT(SUMIF(INDIRECT("sheet"&{1,2,3}&"!A1:A3 "),"<Bill",INDIRECT("sheet"&{1,2,3}&"!B1:B3") )) Now, if your real sheet names aren't Sheet1, Sheet2, Sheet3... List the sheet names in a range of cells like H1:H3 then: =SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H3&"'!A1:A3"),"< Bill",INDIRECT("'"&H1:H3&"'!B1:B3"))) Biff Microsoft Excel MVP "joe" wrote in message oups.com... I have a really tough question that I hope someone can help me with. Basically, I need to sum up data from three worksheets, but only if a certain condition is met. For example, In 3 worksheets, I have data in B1, B2, B3. A1, A2 and A3 have 3 names in them in each worksheet. Now, I know how to take the sum of each worksheet, but I only want to take the sum of everything excluding cell B2 which is Bill in the example below. The data below appears exactly like this in all three worksheets. sheet1 A1 A2 A3 john bill John B1 B2 B3 12 45 13 sheet2 A1 A2 A3 john bill John B1 B2 B3 12 45 13 sheet3 A1 A2 A3 john bill John B1 B2 B3 12 45 13 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |