Need help with find and calculate

  • Thread starter Thread starter mlathem1
  • Start date Start date
M

mlathem1

Ok, scratching a bald spot on my head, and still nothing. I have 5
sheets on a work book. I need to find all values that =2575 in colum
A. When it finds the values if they exist, sum the specified cell fro
all pages. To better explain, 2575 is a billing number, the sheets ar
timesheets. I want to find everyone who listed 2575 as a billin
number. If it is listed, have the formula go across about 12 cells an
find total hours for that billing number. I will have a summary shee
at the end to list specific billing numbers, 2575, 3123,3124, and s
on, with sum of all hours beside it. Can some please help we wit
this.

Thanks
 
Hi
that is difficult (IMHO).
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). Note: I haven't tried this in much detail -> so no
guarantee it will work. e.g. you may use the following formula

=SUM((THREED('sheet2:sheet50'!A1:A999)=2575)*(THREED('sheet2:sheet50'!B
1:B999)))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains the value 2575
 
One way, put the sheet names of all the sheets that you want to sum for
2575,
insert>name>define and call it MyRange, now you can use a formula like

=SUMPRODUCT(SUMIF(INDIRECT("'"&MyRange&"'!A2:A500"),2575,INDIRECT("'"&MyRang
e&"'!M2:M500")))

I assumed M as the column with the values you want to sum for 2575,
replace 2575 with a cell reference for better usability, in that cell put
the billing number you want to sum for. Note that MyRange had to contain all
the sheet names you want this for, not only the first and last
 
Back
Top