Sumifs values not in range

[AthmanArumugam]

Hi,

I have a situation where I need to Sumif values which are not in range.
ie Sum if values in B1,D1,F1,G1....How can I achieve this without using the named range.
Kindly let me know.

 

[AlanSidman]

Please post an example of what you are trying to do specifically. Your current explanation is missing some details that will allow for an answer. A sample spreadsheet would be very helpful.

 

[Hui]

If you want to Sum values in Odd Columns:
=SUM(IF(MOD(COLUMN(C2:Z2),2)=1,C2:Z2,)) Ctrl+Shift+Enter
or
=SUMPRODUCT((MOD(COLUMN(C2:Z2),2)=1)*(C2:Z2)) Enter
OR
=SUMPRODUCT((ISODD(COLUMN(C2:H2))*(C2:H2))) Enter

If you want to Sum values in Even Columns:
=SUM(IF(MOD(COLUMN(C2:Z2),2)=0,C2:Z2,)) Ctrl+Shift+Enter
or
=SUMPRODUCT((MOD(COLUMN(C2:Z2),2)=0)*(C2:Z2)) Enter
OR
=SUMPRODUCT((ISEVEN(COLUMN(C2:Z2))*(C2:Z2))) Enter

 

[AthmanArumugam]

Hi ,

I would want to calculate the %done for each row in the table List of Projects.
I am planning to put a formula in LP20 to add the %completed and then divide it by the number of mappings to get the overall %done.
ie: =Sum(J20,T20,AD20,AN20,AX20....LH20)/CountIf(I20,S20,AC20......LG20,"<>0")

 

[bosco_yip]

Try……….

In LP20, copy down :

=SUMIFS($D20:$LO20,$D$19:$LO$19,"%Completed")/COUNTIFS($D$19:$LO$19,"MappingName",$D20:$LO20,"<>0")

Regards

 

[John Jairo V]

Hi to all!

Another option could be:
=AVERAGEIFS(D20:LO20,D$19:LO$19,"%Completed",C$19:LN$19,"MappingName",C20:LN20,"<>0")

Blessings!

 

[AthmanArumugam]

Thanks Alan, Hui, Bosco and John. Your solutions have worked. Being happy to be a part of this forum. I am finally using =AVERAGEIFS(D20:LO20,D$19:LO$19,"%Completed",C$19:LN$19,"MappingName",C20:LN20,"<>0")

 

[AthmanArumugam]

Hi All,
I applied the same formula in a different dashboard but it is not working.
I tried much to resolve. Could you kindly refer to the formula populated in column LP20 --> LP24 in calculations sheet.

 

[John Jairo V]

Hi again, @AthmanArumugam !

Your ranges must have the same length. Check formulae:

=AVERAGEIFS($D20:$LK20,$D$19:$LK$19,"%Completed",$C$19:$LJ$19,"MappingName",$C20:$LJ20,"<>0")%

Blessings!

 

[AthmanArumugam]

Hi again, @AthmanArumugam !

Your ranges must have the same length. Check formulae:

=AVERAGEIFS($D20:$LK20,$D$19:$LK$19,"%Completed",$C$19:$LJ$19,"MappingName",$C20:$LJ20,"<>0")%

Blessings!

Thank you so much John Vairo. I Love you. I was breaking through this all night yesterday. need to enhance my formula handling skills more.