Count Various Entires in the same Colulm

[CherryM2015]

I need to count how many times a rep has a renewal or an additional quote

Example - I would like to know how many renewals or additional quotes for AL

I have a table on another tab where I will be inserting the formula to give me the totals but I cant figure out the correct formula. I know how to add to the formula to take the data from another tab and the specific column I am unsure how to select Additional or Renewal to give me the total value for both

Example Data:
REP Source
AL PM
AL Renewal
AL PM
AL Additional
AL DW
AL DW
AL Renewal
AL DW

Any help is appreciated
thanks in advance

[bmouse]

Can you post the expected result for better understanding?

[CherryM2015]

I should show that AL has 3 additional or renewal quotes. I can work it out with a separate formula which will show renewal 2 and additional 1 but I wanted to include both in the same cell

hope that makes sense
thanks!

[DBY]

Hi
Try using SUMPRODUCT, this will work with an OR operator (+):

Please Login or Register  to view this content.

Here your sample data is in A2:B10.

DBY

[AlexandraT]

I am assuming you are using countifs as 2 separate formulas. You can add them in the same cell and you would get 3. Like this:

=COUNTIFS(A:A,"AL",B:B,"Renewal")+COUNTIFS(A:A,"AL",B:B,"Additional")

Anything that is between quotes "" can be replaced with any cell that contains the information.

Alex

[CherryM2015]

Thank you DBY and AlexandraT - Both formulas have worked! Great help!

[DBY]

Thank you for the rep mark.

[CherryM2015]

Can you help again please??

Im now trying to work out the vales for the additional and renewals - I have this formula =SUMIFS('2015'!U:U,'2015'!G:G,"AL",'2015'!T:T,"Yes")*(('2015'!H:H="Additional")+('2015'!H:H="Renewal"))

But its adding up all the values for AL when I just want the values for the additional and renewal
'2015' is the sheet the main data is on
U:U us the value column
G:G is the column the sales rep is in
H:H is if it says renewal or additional
T:T is the column to say yes or no its been quoted

Its saying 3 quotes which is correct but adding up all values and not just those 3

[DBY]

Hi
SUMIFS like COUNTIFS only works with 'And' that's why Alexandra added them to get a result. For the above try:

Please Login or Register  to view this content.

This once again is an 'Or' criteria. Here you are only totaling 'Additional' and 'Renewal'. It's not a good idea however, to reference entire columns, not very efficient. Try using a Table or dynamic range or one more limited as I have done.

DBY

[CherryM2015]

Hi,

That's great thanks, although it isn't picking up the ones just for AL - how to I insert that into the formula?

Thanks for the info on referencing entire columns

CM

[DBY]

Try this amended for AL:

Please Login or Register  to view this content.

[CherryM2015]

Absolutely perfect! Thank you so much

[DBY]

You'r welcome. AL must be a pretty important guy!

[CherryM2015]

Ha! He's alright! I have others but I can just amend the formula now for everyone else