=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.

✔ 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 - Awardedyou 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 BuilderA 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 Statesyou 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 BuilderA 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 fromExcel - 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

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

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.

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 BuilderIf 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

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.

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

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"

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 - Awardedyou 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 BuilderA 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 Statesyou 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 BuilderA 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 fromExcel - 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.

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

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.

Ask Your Question

Weekly Poll

Do you find Google Chrome to be a significant drain on your system resources?

Discuss in The Lounge

Poll History