|
Does SUMIF work over multiple worksheets?
I'm trying to create a summary worksheet involving figures from other
worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
I'd heard that SUM works over multiple worksheets, but does SUMIF?
By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
'SUMIF with multiple sheets...SUM ColB IF ColA match with cell C2 of current
sheet =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C2, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'If you have more sheets names as Sheet1,Sheet2,Sheet3 ....Sheet25 try =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25")) &"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 25"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
I've used SUMPRODUCT in the past but wasn't sure if it was needed in this case.
Specifically, I need the total costs (in column F of the worksheet "TRANSACTIONS") of all entries coded as object #555 (in column L of the same worksheet "TRANSACTIONS") to appear in a cell in the worksheet "TOTALS". It seemed a simple case of using SUMIF, but it wasn't working like I thought it would, which is what prompted my original question. Any help would be appreciated, and thank you! "Jacob Skaria" wrote: 'SUMIF with multiple sheets...SUM ColB IF ColA match with cell C2 of current sheet =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C2, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'If you have more sheets names as Sheet1,Sheet2,Sheet3 ....Sheet25 try =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25")) &"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 25"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
Try this...
=SUMIF(Transactions!L:L,"object #555",Transactions!F:F) -- Biff Microsoft Excel MVP "luttona" wrote in message ... I've used SUMPRODUCT in the past but wasn't sure if it was needed in this case. Specifically, I need the total costs (in column F of the worksheet "TRANSACTIONS") of all entries coded as object #555 (in column L of the same worksheet "TRANSACTIONS") to appear in a cell in the worksheet "TOTALS". It seemed a simple case of using SUMIF, but it wasn't working like I thought it would, which is what prompted my original question. Any help would be appreciated, and thank you! "Jacob Skaria" wrote: 'SUMIF with multiple sheets...SUM ColB IF ColA match with cell C2 of current sheet =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C2, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'If you have more sheets names as Sheet1,Sheet2,Sheet3 ....Sheet25 try =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25")) &"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 25"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
Try
=SUMIF('TRANSACTIONS'!L:L,555,'TRANSACTIONS'!F:F) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I've used SUMPRODUCT in the past but wasn't sure if it was needed in this case. Specifically, I need the total costs (in column F of the worksheet "TRANSACTIONS") of all entries coded as object #555 (in column L of the same worksheet "TRANSACTIONS") to appear in a cell in the worksheet "TOTALS". It seemed a simple case of using SUMIF, but it wasn't working like I thought it would, which is what prompted my original question. Any help would be appreciated, and thank you! "Jacob Skaria" wrote: 'SUMIF with multiple sheets...SUM ColB IF ColA match with cell C2 of current sheet =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C2, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'If you have more sheets names as Sheet1,Sheet2,Sheet3 ....Sheet25 try =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25")) &"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 25"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
That's it! I was using "=555" in the syntax rather than just 555.
Thank you! "Jacob Skaria" wrote: Try =SUMIF('TRANSACTIONS'!L:L,555,'TRANSACTIONS'!F:F) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I've used SUMPRODUCT in the past but wasn't sure if it was needed in this case. Specifically, I need the total costs (in column F of the worksheet "TRANSACTIONS") of all entries coded as object #555 (in column L of the same worksheet "TRANSACTIONS") to appear in a cell in the worksheet "TOTALS". It seemed a simple case of using SUMIF, but it wasn't working like I thought it would, which is what prompted my original question. Any help would be appreciated, and thank you! "Jacob Skaria" wrote: 'SUMIF with multiple sheets...SUM ColB IF ColA match with cell C2 of current sheet =SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C2, INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B"))) 'If you have more sheets names as Sheet1,Sheet2,Sheet3 ....Sheet25 try =SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25")) &"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 25"))&"'!B:B"))) If this post helps click Yes --------------- Jacob Skaria "luttona" wrote: I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? |
Does SUMIF work over multiple worksheets?
Okay, so I am looking to sum all values in column F on worksheets 2-30 when
the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . |
Does SUMIF work over multiple worksheets?
Okay, so I am looking to sum all values in column F on worksheets 2-30 when
the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
No, the names are numbers for vehicles in our fleet. The first is '2' and
the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
Does SUMIF work over multiple worksheets?
If your sheet names follow some sort of sequential naming pattern like
Sheet1, Sheet2, Sheet3 or Week1, Week2, Week3 then you can code the sheet names directly into the formula. Since your sheet names do not follow this kind of pattern then you'll have to list the sheet names in a range of cells. So, let's assume you list the sheet names in the range X1:X10. Then to SUMIF across those sheets: =SUMPRODUCT(SUMIF(INDIRECT("'"&X1:X10&"'!A2:A10"), A2,INDIRECT("'"&X1:X10&"'!F2:F10"))) This evaluates to a SUMIF for each sheet of the form: SUMIF('sheet_name'!A2:A10,A2,'sheet_name'!F2:F10) -- Biff Microsoft Excel MVP "Jeremy Lott" wrote in message ... No, the names are numbers for vehicles in our fleet. The first is '2' and the last is '76' and there are 54 others in between those two numbers (5, 11, 12, etc.) I am creating a vehicle log for repairs and would like to total my mechanic's time for each day on a time sheet for him on the first sheet, and track PO's for repairs and his maintenance hours for each vehicle on separate sheets. "T. Valko" wrote: I am looking to sum all values in column F on worksheets 2-30 Are the *real* sheet names 2, 3, 4, 5, etc., 30 ? -- Biff Microsoft Excel MVP "Jeremy Lott" <Jeremy wrote in message ... Okay, so I am looking to sum all values in column F on worksheets 2-30 when the value in column A on worksheets 2-30 equals the value in A2 on worksheet 1. Is this still a sumif equation? Please help! "T. Valko" wrote: I'd heard that SUM works over multiple worksheets, but does SUMIF? By itself, SUMIF can't be used across multiple sheets unless you do something like this: =SUMIF(First_sheet)+SUMIF(Second_sheet)+SUMIF(Thir d_sheet)+etc., etc. However, you can use SUMIF combined with other functions to do this. Or, depending on the application you might be able to use a FREQUENCY formula. How about some specific details *including* the sheet names. The sheet names can make a big difference as to how "easy" this is to implement. -- Biff Microsoft Excel MVP "luttona" wrote in message ... I'm trying to create a summary worksheet involving figures from other worksheets, but SUMIF is giving me problems. I'd heard that SUM works over multiple worksheets, but does SUMIF? . . |
| All times are GMT +1. The time now is 11:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com