SUMIF based upon worksheet name?

[rlexcel101]

Hoping some of you can help.

Say I have the following for example:

Month: July

Total
1 Bob
2 Jim
3 Josh
4 John

Worksheet Names: July, June, May, April, etc.

On each worksheet, say I have the order of names with the number codes next to them. How do I sumif based upon the month? So if I type in June instead of July the values then change to those in the June instead of July.

Do I need VBA?

 

[Joe4]

Jan Feb Mar April...
1. Cash. X


I need to sum up all “Cash” from the tabs which are coded by criteria “1”.

This is the part that is confusing to me, probably because it is unformatted and all runs together.
I also don't see any numbers there, so aren't sure what is being summed.

I would REALLY like to see an actual example with numbers (that can be read), and your expected results so I can more clearly see exactly how your data is structured and what your expected results are.

If you are unwilling/unable to download the programs to assist you in posting nicely formatted images, you can manually use the Code tags, which will maintain spacing, and use the Preview button (in the Advanced editor) over and over to make sure it looks good before posting, i,e,

Name        Age       Score
Adam         37         91
Betty        46         87
Chris        24         72

 

[rlexcel101]

This is the part that is confusing to me, probably because it is unformatted and all runs together.
I also don't see any numbers there, so aren't sure what is being summed.

I would REALLY like to see an actual example with numbers (that can be read), and your expected results so I can more clearly see exactly how your data is structured and what your expected results are.

If you are unwilling/unable to download the programs to assist you in posting nicely formatted images, you can manually use the Code tags, which will maintain spacing, and use the Preview button (in the Advanced editor) over and over to make sure it looks good before posting, i,e,

Name        Age       Score
Adam         37         91
Betty        46         87
Chris        24         72

Ok sorry I’ll try the Code tags

 John/Josh/Joe/Total (Drop Down)

A10            B10               Jan.  Feb.  Mar. Apr
  1        Account- Cash

My back up tabs are then named by each corresponding name and set up in the exact same format. I have the Account Cash code by 1. I am trying to sum all the data with “1” being a criteria. Only thing is I do not have a tab labeled “Total”. Hope this helps

 

[Joe4]

OK, I will try one more time. I want to see an actual example with real number, showing 2 images:
1st image - actual data (with values) - this is the source data
2nd image - your expected output based on the source data (so we can clearly see where those numbers are being derived from)

Remember, while your problem is very familiar to you because it is front of you, we do not have access to that. All that we have to go on is what you provide us. So it is important to be as detailed as possible.
Providing actual examples goes a long way, and often explain things more clearly that a few sentences.

 

[rlexcel101]

OK, I will try one more time. I want to see an actual example with real number, showing 2 images:
1st image - actual data (with values) - this is the source data
2nd image - your expected output based on the source data (so we can clearly see where those numbers are being derived from)

Remember, while your problem is very familiar to you because it is front of you, we do not have access to that. All that we have to go on is what you provide us. So it is important to be as detailed as possible.
Providing actual examples goes a long way, and often explain things more clearly that a few sentences.

Actual Data

 Tab Name- John
A10.          B10                  Jan Feb. Mar
 1.           Account- Cash.     5.    10  8

 Tab Name- Josh
A10.          B10                  Jan Feb. Mar
 1.           Account- Cash.     1    10  8

Output Tab

 Dropdown- John/Josh/Total- if choose a Total 

A10.          B10                 Jan Feb. Mar
 1.           Account- Cash.    6    20. 16

 

[Joe4]

The Total is going to be tricky if you have either an unknown (changing) number of tabs, or a lot of tabs (unless the tabs were all structured so that the all the data lines up exactly in the same rows/columns on every tab, where you could 3D referecing, instead of VLOOKUPS, as shown here: https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/).

Otherwise, I don't think I would try to do the Totals on the same tab as the lookup (I think that choice could make the formulas very long and complex). I would recommend thinking of having a separate tab for the Totals (I am still not certain the best way to get the totals, if the data does not line up and you have to do lookups across all the tabs).

Personally, it sounds like what you really have is a database scenario, a situation in which a database program like Microsoft Access handles much better. If I was tasked with doing this, I would probably opt to try to use that instead of Excel.

 

[rlexcel101]

The Total is going to be tricky if you have either an unknown (changing) number of tabs, or a lot of tabs (unless the tabs were all structured so that the all the data lines up exactly in the same rows/columns on every tab, where you could 3D referecing, instead of VLOOKUPS, as shown here: https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/).

Otherwise, I don't think I would try to do the Totals on the same tab as the lookup (I think that choice could make the formulas very long and complex). I would recommend thinking of having a separate tab for the Totals (I am still not certain the best way to get the totals, if the data does not line up and you have to do lookups across all the tabs).

Personally, it sounds like what you really have is a database scenario, a situation in which a database program like Microsoft Access handles much better. If I was tasked with doing this, I would probably opt to try to use that instead of Excel.

Unfortunately I do not have Access

So say I do the totals on separate tabs, how would my formula look for just each individually?

 

[Joe4]

So much depends on what the data looks like and the changing nature of the workbook.

How the formulas on the Totals tab need to be written all depends on things like:
1. How many tabs there are?
2. Are the number of tabs static, or might that change?
3. Are all the sheets strcutured exactly the same, so the Cash amount is on the same line on every tab?

 

[rlexcel101]

So much depends on what the data looks like and the changing nature of the workbook.

How the formulas on the Totals tab need to be written all depends on things like:
1. How many tabs there are?
2. Are the number of tabs static, or might that change?
3. Are all the sheets strcutured exactly the same, so the Cash amount is on the same line on every tab?

I have 10 tabs and all the data will be on the same lines.

 

[Joe4]

I have 10 tabs and all the data will be on the same lines.

Then, for the Totals page, you should be able to do a 3D reference sum, as shown in the link I provided up in post 15.