Sum(if statement , if statement, Range)

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
112
Hi,

Just wondering for the below formula, if say the particular look up requirement is blank, is it possible to ignore the corresponding if statement? At present, it only matches when the lookup area with content.

Sum(if statement , if statement, Range)

For instance,

=SUM(IF('[Workbook 1.xlsx]Current'!$C:$C=$B$2,IF('[Workbook 1.xlsx]Current'!$D:$D=$C$2,IF('[Workbook 1.xlsx]Current'!$E:$E=$D$2,IF('[Workbook 1.xlsx]Current'!$M:$M=M12,'[Workbook 1.xlsx]Current'!$R:$R)))))

Let us say if $B$2 is blank and is it possible for the formula to by pass this {IF('[Workbook 1.xlsx]Current'!$C:$C=$B$2} and continue to work out the rest? I tried istext or isnumber but did not work.

The reason i want this because [Workbook 1.xlsx]Current'!$C:$C is year, '[Workbook 1.xlsx]Current'!$D:$D is Quarter and '[Workbook 1.xlsx]Current'!$M:$M is month, so if any of this is bypassed then I can look up multiple months, quarter and year etc.

thanks in advance.

Peter
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi

Use:

=SUM(IF(('[Workbook 1.xlsx]Current'!$C:$C=$B$2)+('[Workbook 1.xlsx]Current'!$C:$C=""),...

Remark: these types of formulas are usually inefficient when used with whole columns.
Instead of, for example, $C:$C you'd be better off using something like like $C2:$C1000
 
Upvote 0
Hi pgc01,

Thanks for your help.

Actually Workbook 1.xlsx]Current'!$C:$C all contain values (None of them is blank) but when i set up $B$2 IS Blank I would like the formula bypass {if('[Workbook 1.xlsx]Current'!$C:$C=$B$2)} or select everything from '[Workbook 1.xlsx]Current'!$C:$C with the criteria from other IF(formualrs)

Cheers,

Peter
 
Upvote 0
Hi pgc01,

Thanks,

Yes I tried but it seems like it will only work when B$2 is blank. It will sum the value excludes the data fits criteria if('[Workbook 1.xlsx]Current'!$C:$C=$B$2) when $B$2 contain a value. It will be nice to have a formular that to pick up the value fits to if('[Workbook 1.xlsx]Current'!$C:$C=$B$2 if there is a value in $B$2.

For instance if the value is in $B$2 is Sep 2018 and there is another criertia if statement in the
formula is that IF(('[Workbook 1.xlsx]Current'!$B:$B=$A$2, the value is $A$2 is Q3 2018

So for now when $B$2 is blank the sum formular grabs the value from the month from Jul, Aug Sep 2018 which is correct but when $B$2 contain value of "Aug 2018", then the formular grabs the data from the month of July 2018 and Sep 2018.

Any suggestion on this?

Cheers,

Peter
 
Upvote 0
Hi Peter

I'm sorry, you are right, it should be:

=SUM(IF(('[Workbook 1.xlsx]Current'!$C:$C=$B$2)+($B$2=""),...

which means, either the C value is equal to B2 or B2 is empty or with a null string.
 
Upvote 0
Hi pgc01,

Thanks a lot and this is great!!!

so the plus sign stands for or and if $B$2 = "" then the formular just being ignore? this is great and sorry I am just trying to understand a bit more about the formular. You guys are so amazing!!!

Cheers,

Peter
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top