COUNTIFS function not working correctly?

[smc5917]

Hi there,

I am having some trouble using the COUNTIFS function. I want to determine whether a certain combination of responses occurs in cell pairs. For example:

= SUM(COUNTIFS('P2'!B7,{"Sometimes","Often","Always"}, 'P2'!C7,{"Sometimes","Often","Always"}))

When I try the formula in full, like the above, I get an output of 0 when it should really be 1. When I break the formula down into SUM(COUNTIFS('P2'!B7,{"Sometimes","Often","Always"})) and SUM(COUNTIFS('P2'!C7,{"Sometimes","Often","Always"})) - I get an output of 1 for each.

The full formula has worked elsewhere in my spreadsheet, so I'm really confused as to what's happening. I have checked for spaces etc, and it all came back fine.

Any help would be very much appreciated!

 

[ExceLoki]

are you able to share a sample of your sheet so we can see what you're working with?
suggest using

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Fluff]

When using two arrays you need to change the separator in one of them like

= SUM(COUNTIFS('P2'!B7,{"Sometimes","Often","Always"}, 'P2'!C7,{"Sometimes";"Often";"Always"}))

 

[smc5917]

Unfortunately not able to set up XL2BB as on a work device with limited permissions.
I have attached an example

When using two arrays you need to change the separator in one of them like

= SUM(COUNTIFS('P2'!B7,{"Sometimes","Often","Always"}, 'P2'!C7,{"Sometimes";"Often";"Always"}))

Wow - thank you so much for such a quick, clear and helpful reply. That's solved it

 

[Fluff]

Glad to help & thanks for the feedback.