# Expanding COUNTIF v COUNTIFS Functionality

##### Active Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Scott Huish

##### MrExcel MVP
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)

##### Active Member
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

##### Active Member
Scott,

=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

##### MrExcel MVP
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))

##### Active Member
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.

Replies
9
Views
3K
Replies
16
Views
1K
Replies
5
Views
347
Replies
12
Views
905
Replies
3
Views
751

1,190,742
Messages
5,982,696
Members
439,790
Latest member
jonaust

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back