Countifs not working due to range ? maybe an array?

[mtleigh]

Hi and good afternoon,
I am struggling to get a formula for countifs to work and i beleive this is due to my criteria ranges not being of the same size. Is there any work around to this?

i have put my example below but the actual data set is much larger but i have the same problem. the data set contains 15k rows and the worksheet is approx 25 columns in width.

so i have a table showing customer acc number and the service codes that may have been carried out throughout the year per customer per consignment hence why the same customer number may appear more than once. In the example above, the top table is my data set and the bottom of the 2 tables is a summary i am trying to get to work. I have tried named ranges instead of selecting the columns but this does not work either. If i try countif and count only the number of times the ACC appears it works, if i try countif to see how many times the service code appears, that also works, but i cannot get both to work in tandem using the countifs formula. I did read that both criteria ranges need to be of the same size. i did try then extending my acc criteria range to match the same number of columns but then it was only counting the first column of the service code range and not the other columns. Can anybody help?

 

[mtleigh]

ok - i see youhave made it work but using the wrong service code columns. It should be columns Q to Z but when i change to those columns i just get N/|A

 

[etaf]

i have N/A errors in most cells Q to Z - and i will NOT run any macros

 

[mtleigh]

i have managed to get it to work. I chnaged the formula so that N/A# came out as "" instead and now it works for the correct columns. thankyou all for your help. It would be good if i could get it to work using the tbale names

 

[Fluff]

Did you try the formula I posted (albeit on the wrong columns) that uses structured references.

 

[etaf]

It would be good if i could get it to work using the tbale names

yea, as i say - i see loads of issues on various forums where people use tables names and get wrong results - I have never bothered looking into why -
I will leave that to other members , as it should work , as @Fluff posted earlier

and my example uses the table reference and the range reference , and table returned all zero - so as i say - Sorry no idea