And because of this you can’t use VSTACK for the range argument because VSTACK returns an array, not a range. However, what if you wanted to sum the data for the Clothing category on those three sheets? You might try to write this SUMIF formula: =SUMIF('2020:2022'!D2:D6,'2020:2022'!B2:B6="Clothing",'2020:2022'!D2:D6)Īnd you’d find it returns the #VALUE! error because the SUMIF function requires a range as the first argument. Here’s list of all functions that support 3D references. We can do this because the SUM function supports 3D references. Here we used VSTACK to append it together into one array with the following formula: =VSTACK('2020:2022'!A2:D6)Īnother common 3D formula is to sum the data on those three sheets like so: =SUM('2020:2022'!D2:D6) Remember, a 3D reference is one that references multiple sheets, like we looked at in example 6: One of the advantages of the ‘STACK’ functions is they can be used to make 3D references work with any function that doesn’t have support, except those functions that require a range as an input. VSTACK and HSTACK 3D Reference Limitations Notice the use of the two double quotes in the middle HSTACK argument to allow for a blank column between the two groups of data: The example below shows how we can combine two vertically arranged sets of data into a horizontal array. HSTACK is the horizontal stacking equivalent of VSTACK, so we’ll just look at one scenario for completeness.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |