Summing based on multiple criteria, multiple sheets

M.E. MAD said:

I am attempting to sum based on checking two different columns on each sheet within an entire workbook, but when I finish my formula all I get is 0 for an answer. Below is an example of what I'm trying to do.



=sum(if(e22:e32=0832,if(f22:f32='3p001lf',r22:r32,0)))

The above is the formula for a single sheet, but when I attempt to do the same thing for multiple sheet checking I get nothing but errors. Please Help!

Thanks,


It needs to be array entered

=sum(if(e22:e32="0832",if(f22:f32="3p001lf",r22:r32,0)))


or normally entered, assuming 0832 is text, if not omit the ""

=SUMPRODUCT((E22:E32="0832")*(F22:F32="3P001LF"),R22:R32)

Click to expand...

M.E. MAD said:

Thanks for the reminder about doing it as an array. The other problem I'm having is how to write the part where I am identifying ranges from multiple sheets. Thanks, in advance.

Mike

Click to expand...

M.E. MAD said:

Here is the formula I attempted to create:

{=sum(if(sheet1:sheet5!e22:e32=0832,if(sheet1:sheet5!f22:f32='3p001lf',sheet1:sheet5!g22:g32,0)))

In laymans terms, here is what I'm attemtping to do:

I have a cell where I need to check a range (e22:e32) from all sheets to see if a location code exists. If it does, I then need to check for a job order number (3p001lf) in another column. if both exist, I then need to sum all hours charged based on a third colum(g22:g32). Hopefully this is enough info to give insight on where I seem to be going wrong.

note:
I always seem to get zero. The sheet numbers are shown in place of actual sheet names. Thanks again for all the help.

Click to expand...

M.E. MAD said:

Aladin,

You are a savior. I did exactly as you posted and was able to get working formulas into my spreadsheet. Thanks!

Mike

p.s. what do the -- symbolize in the formula
=SUMPRODUCT(--(THREED('0105:0628'!$F$22:$F$32)=X22),--(THREED('0105:0628'!$J$22:$J$32)=Z22),THREED('0105:0628'!$N$22:$N$32))

Click to expand...