Sum(if statement , if statement, Range)

[peter8848]

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

 

[pgc01]

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

 

[peter8848]

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

 

[pgc01]

Hi Peter

Sorry, I don't understand.

Did you try my solution and did it work?

 

[peter8848]

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

 

[pgc01]

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.

 

[peter8848]

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