Forum Discussion

Benjamin B's avatar
Benjamin B
Copper Contributor
Nov 16, 2017

Countifs based on data that is cross referrenced with other data

I am trying to count the number of calls I receive to a specific phone number based on the area code from which the call came. I have multiple 800 numbers for my office and therefor I need to know how many calls each 800 number gets based upon the state (area code) of the callers. Below is sample data. I can't seem to get countifs to do the trick.

 

 

 

What I am looking to do is have the number of calls that came into each 800 number appear next to that number based upon the area they came in from, allowing me to see how many calls from each state used each 800 number. Below is an example of the desired result. 

 

 

I understand that this may be too complex for Excel, but ANY help would be greatly appreciated. Maybe someone knows a combination of functions I haven't thought of. 

 

Thanks.

Excel Help
Formulas & Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 17, 2017

    Hi Benjamin,

     

    Much better to organise your data into the tables.

    With range like on your screenshot the formula could be like

    =SUMPRODUCT(($B$2:$B$100=$B2)*(COUNTIF($G$1:$G$10,$A$2:$A$100)>0))

    I didn't test it properly, that's time consuming to repeat your screenshot into the file, hope will work.

    • Benjamin B's avatar
      Benjamin B
      Copper Contributor
      Nov 17, 2017

      Thank you Sergei, it was almost correct. Just had to make one change.

       

      =SUMPRODUCT(($B$2:$B$100=$C2)*(COUNTIF($G$1:$G$10,$A$2:$A$100)>0))

       

      It does work though. Thank you very much. This is going to save me a lot of time at work.

       

      And yes, my data is properly tabled and organized in my spreadsheet, that was just sample data I pasted into my example photos.

Resources