Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
Is it possible to condense the following formula as it is too long. =SUM(IF(sheet1!$A$3=B11,sheet1!B$3)+IF(sheet1!$A$4 =B11,sheet1!B$4)+IF(sheet1!$A$5=B11,sheet1!B$5)).. .etc The problem is that I have around 160 rows on sheet 1 which need adding in. Thanks Phil -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
i assume they all need to equal b11 and if so then add them up try this where a1:a26 is your sum range =SUMIF(A1:A26,B11,A1:A26) -- Zygan ------------------------------------------------------------------------ Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
use = SUMIF(sheet1!A3:A160,B11,sheet1!B3:B160)
( assuming that you have 160 rows.) "phil2006" wrote: Is it possible to condense the following formula as it is too long. =SUM(IF(sheet1!$A$3=B11,sheet1!B$3)+IF(sheet1!$A$4 =B11,sheet1!B$4)+IF(sheet1!$A$5=B11,sheet1!B$5)).. .etc The problem is that I have around 160 rows on sheet 1 which need adding in. Thanks Phil -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
This is a little different, but I think will return the result you
seek.......... =IF(ISNA(VLOOKUP(B11,Sheet1!A:A,1,FALSE)),"Not Listed",VLOOKUP(B11,Sheet1!A:A,1,FALSE)) Vaya con Dios, Chuck, CABGx3 "phil2006" wrote: Is it possible to condense the following formula as it is too long. =SUM(IF(sheet1!$A$3=B11,sheet1!B$3)+IF(sheet1!$A$4 =B11,sheet1!B$4)+IF(sheet1!$A$5=B11,sheet1!B$5)).. .etc The problem is that I have around 160 rows on sheet 1 which need adding in. Thanks Phil -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
Thankyou very much! I'm finally starting to get somewhere! -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
Sorry, I misread your post. Didn't see the + signs, thought they were just
the regular things my eyes see this time of a morning <g Vaya con Dios, Chuck, CABGx3 "CLR" wrote: This is a little different, but I think will return the result you seek.......... =IF(ISNA(VLOOKUP(B11,Sheet1!A:A,1,FALSE)),"Not Listed",VLOOKUP(B11,Sheet1!A:A,1,FALSE)) Vaya con Dios, Chuck, CABGx3 "phil2006" wrote: Is it possible to condense the following formula as it is too long. =SUM(IF(sheet1!$A$3=B11,sheet1!B$3)+IF(sheet1!$A$4 =B11,sheet1!B$4)+IF(sheet1!$A$5=B11,sheet1!B$5)).. .etc The problem is that I have around 160 rows on sheet 1 which need adding in. Thanks Phil -- phil2006 ------------------------------------------------------------------------ phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092 View this thread: http://www.excelforum.com/showthread...hreadid=549415 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Condensing Formula
Hi Phil,
=SUMPRODUCT(--(sheet1!A3:A162=B11),sheet1!B3:B162) You will need SUMPRODUCT if you ever introduce more than one sumif criterion... HTH, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |