Sumif with multiple worksheets

[Guest]

I've got a file with many worksheets and a summary page in the beginning.
I'd like to be able to use the SUMIF function, or another, to add certain
worksheets and not others. I keep getting #value errors. This is the
formula I'm using. =SUMIF(Start:Finish!L3,"IPA",Start:Finish!F3)
Start and finish are worksheets i used to group the others together.
This is what I'm hoping this formula will do.
Look at all the worksheets in cell L3, if that worksheet has IPA in that
cell add the value of F3 in the cell that the formula is located.
Am I using the correct formula and just typing it wrong or should I be using
a different type of formula altogether?
Thanks for any help

Cbh35711

 

[Guest]

Hi,

Unfortunately SUMIF cannot be used across sheets.
Assuming you want to put the result in Start, a workaround would be to
select all sheets except Sheet1 and enter the following formula in cell A1
(or the cell of your choice) :-
=IF(L3="IPA",F3,0)

Then enter in A1 in Start :-
=IF(L3="IPA",F3,0)+SUM(Start:Finish!A1)


Hope this helps,
Matt

 

[Harlan Grove]

Cbh35711 wrote...

I've got a file with many worksheets and a summary page in the beginning.
I'd like to be able to use the SUMIF function, or another, to add certain
worksheets and not others. I keep getting #value errors. This is the
formula I'm using. =SUMIF(Start:Finish!L3,"IPA",Start:Finish!F3)
Start and finish are worksheets i used to group the others together.
This is what I'm hoping this formula will do.
Look at all the worksheets in cell L3, if that worksheet has IPA in that
cell add the value of F3 in the cell that the formula is located.
Am I using the correct formula and just typing it wrong or should I be using
a different type of formula altogether?

Unfortunately you can't do this in a general way unless you use an
add-in. You can do it in a hard-coded way by using a list of worksheet
names. If you have the desired worksheet names in a range or array
named WSLst, you could use a formula like

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSLst&"'!L3"),"IPA",INDIRECT("'"&WSLst&"'!F3")))

Calling INDIRECT with an array argument returns something that
functions like an array of range references. SUMIF and COUNTIF accept
such objects and in turn return array results. The SUMPRODUCT call sums
the array of SUMIF results.

The general/add-in approach requires Laurent Longre's MOREFUNC.XLL
add-in, which is freely available from

http://xcell05.free.fr/english/

It provides an add-in function named THREED which could be used in
formulas like

=SUMPRODUCT(--(THREED(First:Last!L3)="IPA"),THREED(First:Last!F3))

 

[Guest]

Great, thanks for the help.

Cbh35711