Match / CountIfs or other?
Is anyone able to help me with a formula that I have been struggling with for some time now. I would like to calculate the value added score across the whole group of learners from their Projected Gr...
Forum Discussion
Thank you Patrick2788 that works great. When I have used this in my document, it is copying blank cels as 0 into the other sheet. Is there anyway of removing these?
Also, is there a way of using this formula to search across more than one sheet a data row?
Huge thanks,
The easiest way to suppress the 0s being returned is to use custom cell formatting which uses four different slots separated by semicolons:
Positive;Negative;Zero;Text
In this example below, the custom cell formatting code is 0;-0;;General
Notice how the third slot is skipped - there are two consecutive semicolons. Additionally, Excel will shorten the formatting code by dropping "General" but I've included it for illustrative purposes.
Re: filtering for multiple sheets. It's do-able but will require the data be consolidate with VSTACK using a 3D reference.