Menu

SUMIF, 3D FORMULA, EXCEL FORMULA HELP

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.worksheet.functions

Claus Busch Claus Busch is offline

Claus Busch is offline

external usenet poster

Posts: 3,872

Default

SUMIF, 3D FORMULA, EXCEL FORMULA HELP

Hi Olly,

Am Mon, 4 Aug 2014 15:03:40 -0700 (PDT) schrieb :

PROBLEM TWO - we need to be able to add new sheets when we win new projects - I understand the concept of "3D formulas" so have called one sheet START and one sheet END and therefore understand that to add up all of the cells in say P20, I would do =SUM(START:END!P20). This works fine, and if I then place projects between the START and END tabs, the formula works.

HOWEVER, I need to combine the above, with a WHATIF style formula, in that I need it to look at all the sheets, on all the rows, and "IF B20:31" on all sheets (START:END) equals say "OLIVER CROOK" then it adds these up, but not if the name doesn't match.

the above works fine with SUM, but not with SUMIF. On sheet Start in
column A write all the sheet names from the sheets between Start and
End.
So if your sheet names are in A1:A10 try:
=SUM((T(INDIRECT("'"&TRANSPOSE(Start!A1:A10)&"'!B" &ROW(20:31)))="Oliver Crook")*(N(INDIRECT("'"&TRANSPOSE(Start!A1:A10)&"' !P"&ROW(20:31)))))
and enter the formula with CTRL+Shift+Enter
The formula sums the values in P20:P31 if in B20:B31 is your name

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
sumif formula in Excel 2003	Ellen	New Users to Excel	4	February 12th 08 02:43 PM
Nesting a sumproduct formula within a sumif formula.	jerrymcm	Excel Discussion (Misc queries)	2	October 3rd 07 03:35 PM
multiply formula where 1 cell has a (sumif) formula as a result	kcip	Excel Worksheet Functions	1	May 3rd 07 07:41 AM
Is there a MAXIF formula similar to the SUMIF formula?	tlc	Excel Discussion (Misc queries)	2	March 13th 06 08:07 PM
Formula in Excel--SUMIF	Richelle	Setting up and Configuration of Excel	4	April 1st 05 04:49 PM

All times are GMT +1. The time now is 06:09 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement