Forum Discussion
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 ho...
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.
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.