Solved How do I add an additional worksheet to the formula below?

February 11, 2018 at 16:37:17
Specs: Windows 7
=SUM(COUNTIFS('Excel - Awarded'!$AB$4:$AB$1958,{"Awarded to Another Builder"}))

I currently obtain Data from Excel - Awarded but now I wish to add Excel - Awarded East to the formula.


See More: How do I add an additional worksheet to the formula below?

Report •

✔ Best Answer
February 14, 2018 at 20:42:44
We are definitely not on the same page. I feel that I have answered your question yet it seems like you keep asking the same question over and over again.

I'll try one more time. BTW...it shouldn't be me that is providing example data in order to determine your requirements, it should be you, but, like I said I'll try one more time.

Let's say that on sheet Excel - Awarded you have this:

        
         A      B      C    ...                      AB
1
2
3
4                                      Awarded to Another Builder
5                                                    Won
6                                                    Won
...
77                                      Awarded to Another Builder
...
1568                                    Awarded to Another Builder

A basic COUNTIF function will return 3.

=COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder")

I think we can agree on that.

Now let's say that on sheet Excel - Awarded Eastern States you have this:

        
         A      B      C    ...                      AB
1
2
3
4                                                   Won
5                                                   Won
6                                                   Won
...

77                                      Awarded to Another Builder
78                                      Awarded to Another Builder
79                                      Awarded to Another Builder
80                                      Awarded to Another Builder
...
1568                                    Awarded to Another Builder

A basic COUNTIF function will return 5.

=COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder")

I think we agree on that.

Therefore, on those 2 sheets, you have a total of 8 occurrences of "Awarded to Another Builder".

To get that 8, we simply SUM the 2 basic COUNTIFS, just like I did in Response #3:

=SUM(COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder"),
COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

The first COUNTIF will return 3 from Excel - Awarded, the second COUNTIF will return 5 from Excel - Awarded Eastern States. The SUM function will add 3 + 5 and return 8.

You can use the Evaluate Formula feature on the Formulas tab to watch Excel step through that formula one step at a time.

=SUM(COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder"),
COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

then

=SUM(3, COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

then

=SUM(3,5)

then

8

If that is not what you are trying to achieve, then tell me why it's not what you re looking for. You are going to have to provide more detail along with some example data and the expected outcome from that data, just like I did. Providing formulas that don't work doesn't help explain what you are trying to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
February 11, 2018 at 19:06:12
I like to first ask about the structure of that formula. Please keep in mind that we can't see your workbook from where we are sitting nor do we know anything about your process. Perhaps a little background information might help us understand your requirements.

1 - What is the purpose of the braces {} around the string "Awarded to Another Builder"?

2 - Why are you using COUNITFS?

From: https://support.office.com/en-us/ar...

"The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met."

I only see a single range and a single criteria. Why not use COUNTIF?

3 - Why are you using SUM? I only see one argument for the SUM function to evaluate, the aforementioned COUNTIFS. It does not appear that the SUM function serves any purpose.

Am I missing something?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 11, 2018 at 19:48:51
First, This worksheet is providing data which I have transferred into the work book from another programme. The purpose of this formula is to allow me to see the reasons why we have lost a project to another builder. The worksheet has a list of several reasons which correspond with the other programmes data.
The formula I have, allows me to pick up relevant data which has been transferred to a worksheet within this workbook. There is a lot more data within that worksheet.
Second, as I am very new to Formulas, I played around with it until it worked for me so I do not totally comprehend everything I have done.
All I need is to know if I am able to add an additional worksheet to the formula which I have created,
I am always open to any assistance which makes my formula less complex.

Report •

#3
February 12, 2018 at 06:00:54
Thank you for those details. They help, but I guess I should have been more specific in my question. I was wondering about the purpose of the formula that you posted, specifically from an Excel perspective.

As far as I can tell, it will return a count of how many times the string "Awarded to Another Builder" is found in the range $AB$4:$AB$1958 on the sheet Excel - Awarded.

e.g. It will return 3 in this case:

        
         A      B      C    ...                      AB
1                                      Awarded to Another Builder
2                                                    Won
3                                                    Won
...
77                                      Awarded to Another Builder
...
1568                                    Awarded to Another Builder

If that is what you are trying to do, then a basic COUNTIF, without the SUM function and without the braces, should work:

=COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder")

OK, assuming that that is correct, my next assumption is that you want to count the total number of times that the string appears on 2 sheets. If so, then a SUM function with a couple of COUNTIF functions should work. If you separate the arguments for a SUM function with a comma, Excel will SUM them. e.g.

=SUM(A1,B3,C5) or =SUM(1,4,6)

Another option is to use the Addition operator: =A1+B3+C5 or =1+4+6

In your case, you would SUM the results of each COUNTIF:

=SUM(COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder"),
COUNTIF('Excel - Awarded East'!$AB$4:$AB$1958,"Awarded to Another Builder"))

or

=COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder") +
COUNTIF('Excel - Awarded East'!$AB$4:$AB$1958,"Awarded to Another Builder")

If that is not what you are looking for, just let me know and I'll try again.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
February 13, 2018 at 23:56:31
Thank you for that it sure has simplified the Formula. Are you able to advise how I can incorporate the Sum function into the formula.

Report •

#5
February 14, 2018 at 06:06:03
I'm confused again. I already included the SUM function. Once by directly using SUM and again by using the addition operator.

I'm not sure what you are asking about at this point.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#6
February 14, 2018 at 14:51:52
I realise that this is not the correct formula but I hope this describes what I wish to achieve.

=COUNTIF('Excel - Awarded,Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder")

Basically I want to get Awarded Builders from sheets, "Excel - Awarded" and "Excel - Awarded Eastern States"


Report •

#7
February 14, 2018 at 20:42:44
✔ Best Answer
We are definitely not on the same page. I feel that I have answered your question yet it seems like you keep asking the same question over and over again.

I'll try one more time. BTW...it shouldn't be me that is providing example data in order to determine your requirements, it should be you, but, like I said I'll try one more time.

Let's say that on sheet Excel - Awarded you have this:

        
         A      B      C    ...                      AB
1
2
3
4                                      Awarded to Another Builder
5                                                    Won
6                                                    Won
...
77                                      Awarded to Another Builder
...
1568                                    Awarded to Another Builder

A basic COUNTIF function will return 3.

=COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder")

I think we can agree on that.

Now let's say that on sheet Excel - Awarded Eastern States you have this:

        
         A      B      C    ...                      AB
1
2
3
4                                                   Won
5                                                   Won
6                                                   Won
...

77                                      Awarded to Another Builder
78                                      Awarded to Another Builder
79                                      Awarded to Another Builder
80                                      Awarded to Another Builder
...
1568                                    Awarded to Another Builder

A basic COUNTIF function will return 5.

=COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder")

I think we agree on that.

Therefore, on those 2 sheets, you have a total of 8 occurrences of "Awarded to Another Builder".

To get that 8, we simply SUM the 2 basic COUNTIFS, just like I did in Response #3:

=SUM(COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder"),
COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

The first COUNTIF will return 3 from Excel - Awarded, the second COUNTIF will return 5 from Excel - Awarded Eastern States. The SUM function will add 3 + 5 and return 8.

You can use the Evaluate Formula feature on the Formulas tab to watch Excel step through that formula one step at a time.

=SUM(COUNTIF('Excel - Awarded'!$AB$4:$AB$1958,"Awarded to Another Builder"),
COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

then

=SUM(3, COUNTIF('Excel - Awarded Eastern States'!$AB$4:$AB$1958,"Awarded to Another Builder"))

then

=SUM(3,5)

then

8

If that is not what you are trying to achieve, then tell me why it's not what you re looking for. You are going to have to provide more detail along with some example data and the expected outcome from that data, just like I did. Providing formulas that don't work doesn't help explain what you are trying to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
February 15, 2018 at 00:34:29
Please accept my apology as I did not understand your response on February 12 because I did not scroll down to see the complete reply. It now all makes sense and has worked. Thank you so much.

Report •

Ask Question