CODEWITHSUNDEEP
excelexcel-formula
ArnoldB
ArnoldB

Reputation: 165

How to create a variable in my Excel Formula

Instead of me having to type out each name in the array above. How could I put that array into a variable? For example other programming languages I could just do var array = {“dog”,”cat”, “tiger”}. Now my items are stored in array.

=SUMIFS(AttendanceExport!$O:$O,AttendanceExport!$N:$N,'Revenue Output'!$B10,AttendanceExport!$J:$J,{"dog","cat","tiger","bear"}

Is it possible in Excel?

Upvotes: 1

Views: 391

Answers (2)

f_qi
f_qi

Reputation: 699

I'm guessing you are referring to VBA in Excel. 'Dim variable As Integer' is the format to declare a variable.

enter image description here

Here is a link to help you better understand.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

In short, you can't with formulas.

But what you can do is put the values in a range of cells and use this in array:

=SUM(SUMIFS(AttendanceExport!$O:$O,AttendanceExport!$N:$N,'Revenue Output'!$B10,AttendanceExport!$J:$J,$A$1:$A$4))

Where $A$1:$A$4 is the location of the items.

This needs to be entered with Ctrl-Shift-Enter instead of Enter.

If you don't mind the hard coded this can be normally entered:

=SUM(SUMIFS(AttendanceExport!$O:$O,AttendanceExport!$N:$N,'Revenue Output'!$B10,AttendanceExport!$J:$J,{"dog","cat","tiger","bear"}))

Upvotes: 4

Related Questions