Countifs and sumif for text and numbers

[Wilksstephanie1]

Hello! I'm new to this forum and I'm hoping that someone can help me out with a formula that I'm having trouble with. Below is a small recreation of the spreadsheet that I have to work with. I'm trying to count the number of Confidential parent sites that belong to Manager A, B, or C then add that count to the total number of confidential sites that belong any of the three managers. The formula that I have so far is:

COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager A*")+COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager B*")+COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager C*")

I can't figure out how to add the Countifs to the total sum of confidential sites owned by any of the three managers. Any help would be appreciated!
</SPAN>
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Parent Site Classification (Column G)</SPAN>
[/TD]
[TD]Number Of Secret Sites (Column H)</SPAN>
[/TD]
[TD]Number of Confidential Sites (Column I)</SPAN>
[/TD]
[TD]Number of Proprietary Sites (Column J)</SPAN>
[/TD]
[TD]Reporting Chain (Column Z)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Secret</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]Snap, Crackle, Manager B, Pop</SPAN>
[/TD]
[/TR]
[TR]
[TD]Confidential</SPAN>
[/TD]
[TD]8</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]6</SPAN>
[/TD]
[TD]March, June, Manager C, April</SPAN>
[/TD]
[/TR]
[TR]
[TD]Proprietary Sites</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]6</SPAN>
[/TD]
[TD]Skywalker, Manager A, Vader, Solo</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[circledchicken]

Hi and welcome to the forum,

I'm not sure I follow your example - can you explain it a little more? What answer are you expecting from that sample?

 

[Wilksstephanie1]

Sorry for the confusion. I'm trying to find out how many Confidential parent sites belong to Manager A, B, or C

 

[Wilksstephanie1]

Sorry for the confusion and the last post. First, I need to find out how many Proprietary Parent sites (Column G) are owned by </SPAN>Manager A, B, or C (Column Z). Then, I need to take the total count of Proprietary Parent Sites and add that to the total number of Proprietary Sites ( Column G) that belong to Manager A, B, or C. (I also need to do this for Secret and Confidential sites.) </SPAN>

I used the Countif formula for the first part of the formula because the data in the Parent Site Classification column in text instead of a number. I’m having trouble with the second part of the formula (Adding the total count of Proprietary Parent Sites and to the total number of Proprietary Sites that belong to Manager A, B, or C.). Any help would be appreciated!</SPAN>

 

[Wilksstephanie1]

I figured it out and it works perfectly! Here is the soultion:

=(COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager A*")+COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager B*")+COUNTIFS(G:G, "Proprietary",Z:Z,"*Manager C*"))+SUMIF(Z:Z,"*Manager A*", J:J)+ SUMIF(Z:Z,"*Manager B*", J:J)+ SUMIF(Z:Z,"*Manager C*", J:J)


Thanks for taking a look at this circledchicken!!</SPAN>

 

[circledchicken]

I'm glad you found a solution.

Are you sure the "Proprietary" criteria shouldn't also be "*Proprietary*", as the COUNTIFS part of your formula returns zero with the sample data you posted?

Here's a slightly more condensed way you can write it your formula in post #5:

=SUM(
  COUNTIFS(G:G,"Proprietary",Z:Z,{"*Manager A*","*Manager B*","*Manager C*"}))+
 SUM(
  SUMIFS(J:J,Z:Z,{"*Manager A*","*Manager B*","*Manager C*"}))

Note that if you change the formula in the future to have 2 multi-condition criteria groups (the bit surrounded by {}) within a single COUNTIFS or SUMIFS function, this style will only work if you seperate one of the groups with semi-colons ({"..";"..";".."}) and the other with commas ({"..","..",".."}). For more than 2 multi-condition criteria you will need to revert to your original formulation.

 

[Wilksstephanie1]

Thanks, again! This was very helpful!!!

 

[circledchicken]

Thanks, again! This was very helpful!!!

You're welcome!