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]

Let's say that you have a working SUMIF function like this that returns the correct value for Month for the name in cell B4 on the current sheet:

=SUMIF(May!A:A,B4,May!B:B)

Now, let's say that you want to make it dynamic so that instead of hard-coding the sheet name "May" in the formula, you type the sheet name in cell A1, and have the formula reference that.
In order to build dynamic range references, you need to use the INDIRECT function. The formula would look something like this:

=SUMIF(INDIRECT(A1 & "!A:A"),B4,INDIRECT(A1 & "!B:B"))

 

[tico_ocit]

Let's see if this is what you want.
I've created 3 sheets, "Total", "July", "June".





1 - July
2 - June
3 - Total

=SUMIF(INDIRECT($B$5&"!$B$1:$B$4";TRUE);Total!B9;INDIRECT($B$5&"!$A$1:$A$4";TRUE))

So, if you replace "July" for "June" on Total!B5, A9 will change to 3.
Be aware that the tables on each sheet of the months MUST be on the same cells, like I did on the example.

Hope this is what you want.
Hope I could help.

 

[rlexcel101]

Let's say that you have a working SUMIF function like this that returns the correct value for Month for the name in cell B4 on the current sheet:

=SUMIF(May!A:A,B4,May!B:B)

Now, let's say that you want to make it dynamic so that instead of hard-coding the sheet name "May" in the formula, you type the sheet name in cell A1, and have the formula reference that.
In order to build dynamic range references, you need to use the INDIRECT function. The formula would look something like this:

=SUMIF(INDIRECT(A1 & "!A:A"),B4,INDIRECT(A1 & "!B:B"))

Perfect! Thank you

One last question..now let’s say I wanted to add a 2nd criteria. Would it need to be a SUMIFs?

 

[Joe4]

One last question..now let’s say I wanted to add a 2nd criteria. Would it need to be a SUMIFs?

Yes (or you can use SUMPRODUCT).

See: https://www.techonthenet.com/excel/formulas/sumifs.php

 

[rlexcel101]

Yes (or you can use SUMPRODUCT).

See: https://www.techonthenet.com/excel/formulas/sumifs.php

Thanks

I’ll try it out

 

[rlexcel101]

So I’m trying to do a sumproduct sumif based off the drop down now

Say I have:

Name: John, Josh, Steve , Total

Code Jan Feb Mar Apr...so forth
1 Cash

My tab names match the names in the drop down.

How do I do a Indirect to sum based upon the drop down? All my “Cash” is coded by 1 on the backup tabs.

 

[Joe4]

I am not following your revision to the original question.
I think it will be very beneficial if we could see whjat your data actually looks like, and what your expected result is.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[rlexcel101]

I am not following your revision to the original question.
I think it will be very beneficial if we could see whjat your data actually looks like, and what your expected result is.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Basically I have tabs with backup data in same format as Summary page.

Tab Names: John Josh Steve

Summary Page:

D4= where my drop down is with John, Josh, Steve, Total

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


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

So if I choose on Summary page for John I just get the month by month cash for him and if I choose Total I get all three names totaled.

 

[rlexcel101]

Bump