How to: If cell L2 in multiple tabs = 1, then add that 1 to a total

[lauralippay]

I have an Excel file with 58 tabs representing different markets. Each tab has the same set of columns, where the A column is the date (from 7/25/2015 to 11/9/2015), and the L column has a function that makes the value in each cell in that column either 0, 1, or 2.

Example:

AREN worksheet:

A	B	C	D	E	F	G	H	I	J	K	L
7/25/2015											1
7/26/2015											2
7/27/2015											1

ARES worksheet:

A	B	C	D	E	F	G	H	I	J	K	L
7/25/2015											1
7/26/2015											1
7/27/2015											1

ATDE worksheet:

A	B	C	D	E	F	G	H	I	J	K	L
7/25/2015											1
7/26/2015											1
7/27/2015											0

I have a "TOTALS" tab that lists the same dates, 7/25/2015 - 11/9/2015 in the A column, and in the B column I'm attempting (unsuccessfully so far) to add up how many "1's" are in the L column cells on each tab for that date. For example, in the table above, there are 3 worksheets showing the first three dates of each worksheet and the L column values for those dates. The correct function would produce this on the TOTALS worksheet, where *if* the value of the L cell is 1, then it's added to the sum:

A	B
7/27/2015	3
7/27/2015	2
7/27/2015	2

On 7/27, three of the L2 cells in the three worksheets had a value of 1, therefore the value of TOTALS B2 cell is 3.
On 7/28, two of the L2 cells in the three worksheets had a value of 1, therefore the value of TOTALS B2 cell is 2.
On 7/29, two of the L2 cells in the three worksheets had a value of 1, therefore the value of TOTALS B2 cell is 2.

I'd like to say I have something close, but after trying variants of IF's SUMIF's and INDIRECT's, I'm not really even close.

[mongoose36]

Assuming a header row place this in the appropriate cell in the totals sheet.

Please Login or Register  to view this content.

[JeteMc]

The formula that I used is:

Please Login or Register  to view this content.

where $G$3:$G$5 references a table of tab names. Here the formula is applied to a file with your tables on different tabs (the results that you were looking for are highlighted):
Counting values in multiple tabs.xlsx
Let me know if you have any questions.

[lauralippay]

This is great JeteMc - thank you for taking the time to look at this - I appreciate it

I applied this to my worksheet and I'm getting "0".
What I'm not sure of is the end part of the function: &ROW()-1),C$1))

I changed
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$3:$G$5&"'!L"&ROW()-1),C$1))
to
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$E$2:$E$4&"'!L"&ROW()-1),F$1))

based on how I set it up in my worksheet, with the assumption that C$1 is referencing the value "1". So I'm not sure where I've gone wrong in this one, so it may be that my assumptions are incorrect?

TOTALS Worksheet tab:
TOTALS.jpg

AREN Worksheet tab:
AREN.jpg

*actual* values for AREN, ARES and ATDE L2 cells are 1, 1, and 2 respectively (so total should be 2 instead of the 0 I'm getting).

[lauralippay]

Assuming a header row place this in the appropriate cell in the totals sheet.

Please Login or Register  to view this content.

Whew - that'd be lengthy for 58 tabs, but a fallback that does seem to work - thank you!

[JeteMc]

The ROW()-1 was put in there because the value, in my file, was on row one and the formula was on row two.
In the screen shots of your file, both the value and formula are on row 2 so try the following modified formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

[lauralippay]

That's it! So very much appreciated - thanks again JeteMC

[JeteMc]

You're welcome, thank you for the feedback, and welcome to the forum. Please take a moment to mark the thread 'Solved' using the thread tools above your first post. Hope that you have a good day.

[lauralippay]

After getting that in there, I realize I need to add 1 to the sum if the number in the L2 cell is 1 *or if it's 2* (but not if it's anything else). Either 1 or 2 in the L2 cell should add 1 to the sum.

Were I better at these SUMPRODUCT statements, I might not be so perplexed, but I'm once again entirely stuck. Also, I feel like I should buy you a beer for asking for your time.

[JeteMc]

I am not quite sure what you mean. There would be, according to post #5, fifty-eight L2's, so do you add one each time that an L2 has the value or 1 or 2? if so the formula could be modified to:

Please Login or Register  to view this content.

In this scenario the "Totals", from post #1, would be six, four and four.
On the other hand if you are adding 1 only once if any of the fifty-eight L2's has a value of 1 or 2 then the formula could be modified to:

Please Login or Register  to view this content.

In this scenario the "Totals", from post #1, would be four, three and three.

Let me know if you have any questions.

[JeteMc]

Actually going back to the formulas as written for the tabs shown in post #4 they should be:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

respectively. Sorry about that oversight. Let me know if you have any questions.

[lauralippay]

OOH ok- I didnt get into work today so I'll be able to try this out tomorrow. Looking forward to it - thank you @JeteMc!

[lauralippay]

Actually going back to the formulas as written for the tabs shown in post #4 they should be:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

respectively. Sorry about that oversight. Let me know if you have any questions.

Thank you again JeteMc. This is impressive, and interesting to see how this works out.

For what I'm trying to work out (including the 2's in the L2 cells as well as the 1's), the totals for the first example should be 3, 3 and 2, counting "1" for each time a 1 or a 2 is mentioned (where the "1" count is like a "yes" - saying "yes the value of L2 was either a 1 or a 2"). So these are slightly off.. I'm going to see if I can guess what to modify of this lovely function you created. And if that makes sense and you have any ideas on how to modify it, I'm all ears!

Thanks again!

[JeteMc]

OK, I think that I understand now, count the number of 1's and 2's combined. Here is one way to do that:

Please Login or Register  to view this content.

Where the value of B1 is 1 and the value of C1 is 2. Here the formula is used in the file:
Counting values in multiple tabs.xlsx
If I am still misunderstanding what you want then I would suggest using the "Go Advanced" button below the Quick Reply box and the paper clip icon on the resulting page, to upload the file from which the screenshots in post #4 were made (sans any sensitive info). Manually put the expected values in Totals!b2:b4 so It will be clear what a correct formula should yield.
Let me know if you have any questions.

[lauralippay]

That's it! That's it! That's it! Thank you again JeteMc. Simply the best.

[JeteMc]

You're welcome, thank you for the feedback and kind words. Please take a moment to mark the thread 'Solved' using the thread tools above your first post. Hope that you have a good day.

[jason.b75]

JeteMc's method is pretty much the only way to do what you need with the way your data is set up, but purely because I don't like volatile functions (indirect is one of them), here's an alternative.

It's a bit more effort to set up initially, but if your sheet requires you to copy the formula from post #14 to many rows, then you may notice some delay when you make even a small change to your data. This method should eliminate that delay. If this is not going to be a problem for you then, given the number of sheets involved and the time required to set this up, JeteMc's suggestion would be my method of choice as well.

Note that this sheet makes use of an extra column (column M) in each of the data sheets which identifies the rows with a value of 1 or 2 in column M.

[jason.b75]

Just to follow up on a side discussion between JeteMc and myself, there is a slight variation to the formula in post #14 which could possibly be slightly faster to calculate than the original if the volume of data is causing issues.

This is based on the file from post #14.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$3:$G$5&"'!RC12",0),B$1:C$1))

The idea being that the array within the INDIRECT function only needs to be processed once for each formula, not twice. I've also made another slight change to the way the row is added to the indirect function.

Defining the indirect part of the formula as a named range could be another potential performance improvement, testing with and without is the only way to find out for sure.