Excel Formula Conundrum with SUM and COUNTIFS

[Excel_User_10k]

=(SUM(E15/(COUNTIF(New_or_Upgrade,"New"))))

Currently I have am using the above formula to Calculate the percentage of how many of the value in "E15" there are out of a Table of Sales. However, I need it to be more specific and I cannot figure out how to do this without messing up the SUM. I want "E15" to be divided by the Total (where the value of "New" in one Column is the basis and then it counts how many of several criteria in another column that can also include * variable as it is not an exact match).

So something along the lines of:

=(SUM(E15/(COUNTIF(New_or_Upgrade,"New", AND the Sales_Type is A, B and C))))

I cannot find the Formula translation for this and if anything like this does work, it only divides E15 value into the initial "New" criteria.

Hopefully I am explaining myself well and someone has a solution?

 

[jasonb75]

Perhaps

=E15/SUM(COUNTIF(New_or_Upgrade,"New", Sales_Type{"A","B","C"}))

 

[Peter_SSs]

I'm assuming that @jasonb75 meant

=E15/SUM(COUNTIFS(New_or_Upgrade,"New", Sales_Type,{"A","B","C"}))

 

[Excel_User_10k]

Perhaps

=E15/SUM(COUNTIF(New_or_Upgrade,"New", Sales_Type{"A","B","C"}))

Hi Jason, thanks for answering so quickly.

I never would have thought of having E15 and then SUM formula, but this formula isn't working either unfortunately. It doesn't like the second ref "Sales_Type". Which is a real reference, but I think it is because you used COUNTIF rather than COUNTIFS. So it only recognises the first range and criteria.

But then again, it doesn't work with COUNTIFS either. Just comes up the with usual error saying "excel thinks you are trying to write a formula..."

 

[Excel_User_10k]

I'm assuming that @jasonb75 meant

=E15/SUM(COUNTIFS(New_or_Upgrade,"New", Sales_Type,{"A","B","C"}))

Hi Peter,

Yeah I picked up in that but it still isn't working. Keeps highlighting the Sales_Type ref but I don't understand why because it is a valid name and it comes up whilst I am typing it.

 

[Peter_SSs]

Yeah I picked up in that but it still isn't working. Keeps highlighting the Sales_Type ref but I don't understand why because it is a valid name and it comes up whilst I am typing it.

Are you sure that you tried what I posted? As well as using COUNTIFS, it also includes another comma that was missing from Jason's formula.

 

[Excel_User_10k]

Are you sure that you tried what I posted? As well as using COUNTIFS, it also includes another comma that was missing from Jason's formula.

Ah ok. Yeah I missed that. I was looking at it on my phone haha. It is working now. Thanks very much guys

 

[Peter_SSs]

You're welcome. Glad we could help.