Help - Returning errors - want them to still add up or add as 0 if no data there

Good afternoon all,

I'm working on a cash flow, but for a lot more tabs than attached, but just set it to a few tabs. The tabs are fine but it's the consolidated tab I am having issues with.

I'm using a match formula to look up and return the totals of all the tabs for each line description under the corresponding month and return these values across all the tabs in the consolidated.

My issue is:

For example: Rent Income (this only applies to one of the tabs - Property). This line is in consolidated, and I've just copied down the formula to search ALL tabs for this information (I want it so that if this information isn't found, to return this as 0 so that it will still add up to any that are found).

I tried the IFERROR(sum(such and such),0) for each tab but it's not working. I can't put the if error at the very beginning and end of the whole formula as it returns 0 when there is information in one of the tabs.

I hope I'm making sense.

So really, I want "Rent Income" in consolidated tab on cell B19 to search ALL tabs (even though not all will have rent income listed), and it should return the one it finds as £1671.70.

Then I can always just drag formula down when adding additional lines with ease going forward if need be.

Thanks in advance!

UPDATE: Solved - Attachment now removed

Assuming your Excel version is a 365 subscription, then try:

Formula:

Please Login or Register  to view this content.

Look at the formula. It stacks all the available worksheets. I can't guess at what sheets you have excluded, but you just need to change the 3 to reflect the last sheet in your workbook.

Hello,

Thanks for this, this works. I've added this to my main sheet and also the example.

Is there a way I can have this in the formula look at the Text of Rent Income in cell A19 in consolidated, rather than typing in "Rent Income" in the formula itself so if I was to drag this formula down in consolidated, it would look for that line of text?

Also, when dragging this across - how do I have this to look up the month in the tabs as they progress, as I have dragged the formula and it's come up #CALC! - May 24 I've typed in £45, and June £14 to try and get this to pull through on consolidated.

Thanks in advance

You're welcome.

Glad you worked it out.



You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.