Expanding COUNTIF v COUNTIFS Functionality

[isadoko]

I am currently using this formula to count contact types in a given week: =COUNTIFS(ContactType,$C5,Weekof,"=" & AJ$4). It works fine.

Recently the data has changed in two ways 1) changes to spelling of existing contacts types,eg, XTR Agency became TR Agency, 2) replacement of existing contact types, eg, SRC Frontline became PCS Lead. For the meanwhile I need to count both. Inexplicably a modification to my original formula does not work anymore. So I tried these below.

The first is pulling the contact type from list in Sheet1.
=COUNTIFS(ContactType,"=" & Sheet1!F4,Weekof,"=" & AJ$4,ContactType, "=" & Sheet1!F5,Weekof,"="&AJ$4)

The second is a modification of a formula I saw in a posting in ME.COM
=SUM(COUNTIF(ContactType,{"XTR Agency","TR Agency"}))
=SUM(COUNTIF(ContactType,{"XTR Agency","TR Agency"},{Weekof,"=" & AJ$4}))

However, I cannot get the date part to work, ie, weekof... I'd appreciate some help to get this right.
Thanks

 

[Scott Huish]

The parts in the array have to be literal text or values, it can not be a range reference, and you can't combine multiple criteria ranges.
Try this:

=SUM(COUNTIF(ContactType,{"XTR Agency","TR Agency"}))+COUNTIF(Weekof,AJ$4)

If those are the only 2 options that end in TR Agency, you could also do this:

=COUNTIF(ContactType,"*TR Agency")+COUNTIF(Weekof,AJ$4)

 

[isadoko]

Scott,

I tried your solution and it yielded 715 but the actual is 19. At first I forgot to ctrl, shift, enter...then I did but same result. What might I have done wrong?

Thanks

 

[isadoko]

Scott,

I tried this instead

=COUNTIF(CFSD!B:B,{"XTR Agency","TR Agency"})+COUNTIF(CFSD!V:V,"=" & AJ$4)

It counted 17 until I changed the second function to V:V then it reverted to 715. The calculation is not being limited to the date reference in AJ$4.

Thanks

 

[Scott Huish]

OK, now I understand what you are trying to do, try something like this:

=SUMPRODUCT((CFSD!B1:B1000="XTR AGENCY")+(CFSD!B1:B1000="TR AGENCY"),--(CFSD!V1:V1000=$AJ$4))

 

[isadoko]

That worked! I had thought about using SUMPRODUCT but was warned that it is a "heavy" function and as my dataset is 30K+ rows and growing it would slow it down. However, I will take that risk given it solves my problem. Thanks again for your help.