Does SUMIF work over multiple worksheets?

[luttona]

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?

 

[T. Valko]

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(Third_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.

 

[Jacob Skaria]

'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(INDIRECT("1:25"))
&"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:25"))&"'!B:B")))

If this post helps click Yes

 

[luttona]

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!

 

[T. Valko]

Try this...

=SUMIF(Transactions!L:L,"object #555",Transactions!F:F)

 

[Jacob Skaria]

Try
=SUMIF('TRANSACTIONS'!L:L,555,'TRANSACTIONS'!F:F)

If this post helps click Yes

 

[luttona]

That's it! I was using "=555" in the syntax rather than just 555.

Thank you!

 

[Jeremy Lott]

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!

 

[Jeremy Lott]

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]

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 ?

 

[Jeremy Lott]

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.)

 

[Jeremy Lott]

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.)

 

[Jeremy Lott]

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.

 

[Jeremy Lott]

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.

 

[Jeremy Lott]

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.

 

[Jeremy Lott]

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.

 

[Jeremy Lott]

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.

 

[Jeremy Lott]

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]

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)