ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Condensing Formula (https://www.excelbanter.com/excel-discussion-misc-queries/92585-condensing-formula.html)

phil2006

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


Zygan

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


hans bal(nl)

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



CLR

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



phil2006

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


CLR

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



[email protected]

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



All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com