Formula Help

[jahaynes]

I am trying to get the sum expenses on 25 sheets with specific data in a certain column. so say all 25 sheets have the data in column D that I want a total of and look for anything in column C that contains "7605". These numbers in column C could be from 760500-760599. Is it possible to do this? I have tried these formulas and they return 0, but the total should be 55,025.81

=SUMIFS(Atlanta!D:D,Atlanta!$C:$C,"*"&C2&"*")
=SUMIFS(Atlanta!D:D,Atlanta!C:C,"<="&C2,Atlanta!C:C,">="&C2)

this is just trying to get the formula to work with one sheet before trying to add the other 24 to the range.

Can this be done?

 

[jahaynes]

How about

=SUMIFS(Atlanta!D:D,Atlanta!$C:$C,"<>")

That worked too. I just can’t get any of them to work with multiple sheets.

 

[jahaynes]

Why do you need to do that if all numbers are between those limits?
Did you try what I suggested?

This worked for one sheet but doesn’t work for multiple. I just can’t seem to get any of them to work looking for this data on multiple sheets.

 

[jahaynes]

Thank you!!!! That worked!!!

Take that back it worked for on sheet but not working across multiple

 

[jahaynes]

Why do you need to do that if all numbers are between those limits?
Did you try what I suggested?

This worked with one sheet, but did not work with the multiple sheets. I just tried the other that was suggested and that did not work either.

 

[Fluff]

How about

=let(a,vstack(atlanta:washington!C2:D10000),filter(index(a,,2),left(index(a,,1),4)="7605"))

 

[jahaynes]

How about

=let(a,vstack(atlanta:washington!C2:D10000),filter(index(a,,2),left(index(a,,1),4)="7605"))

That worked, but didn't put the total in one cell. Is there something that will total the column D? I am sorry, I just know basic formulas in excel and this one is stumping me

 

[Fluff]

Forgot to add the sum

=let(a,vstack(atlanta:washington!C2:D10000),sum(filter(index(a,,2),left(index(a,,1),4)="7605")))

 

[jahaynes]

=let(a,vstack(atlanta:washington!C2:D10000),sum(filter(index(a,,2),left(index(a,,1),4)="7605")))

YES! That worked. Now one more question and hopefully this is the last. If I have to copy this formula to capture the data in columns E through P, how would I do that? I don't know what all of the parts of this formula mean, so I don't know what to change to get the totals for each of the columns.

 

[jahaynes]

I jus

YES! That worked. Now one more question and hopefully this is the last. If I have to copy this formula to capture the data in columns E through P, how would I do that? I don't know what all of the parts of this formula mean, so I don't know what to change to get the totals for each of the columns.

I figured it out, but now I am running into another issue. I am working through it to see if I can figure out what is wrong with it. Could you possibly explain this formula to me so I can use if for reference in the future?

 

[Fluff]

Please do not mark your post as a solution, you need to mark the post that contains the actual solution. I have changed it for you this time.