COUNTIF Miscalculating Upon Sort

[reberryjr]

I have 2 sheets in the same book that I'm working with. Im trying to count the number of iterations on the Response Details sheet where the value in column G = "NI" and the value in column C matches the value in column E on the Summaries sheet. Column C on the summaries sheet will only have 1 iteration of different values (let's say apple), while column C on the Response Details sheet will have multiple iterations of apple. I've been using this formula, which works until I sort either sheet.

=COUNTIFS('RESPONSE DETAILS'!$C:$C,SUMMARIES!$E133,'RESPONSE DETAILS'!$G:$G,"NI")

I feel as though I need to somehow combine the COUNTIFS with a VLOOKUP, but I'm unsure.

I apologize for any issues with the formatting of this post. For some reason, my work laptop only allows me to enter a thread title, but nothing in the body of a thread, so I had to type this on my mobile.

 

[Fluff]

Assuming the formula is on the summaries sheet use

=COUNTIFS('RESPONSE DETAILS'!$C:$C,$E133,'RESPONSE DETAILS'!$G:$G,"NI")

You should never refer to the sheet name the formula is in.

 

[etaf]

and the value in column C matches the value in column E on the Summaries sheet.

What value are you trying to match
the formula you have used is looking at a criterial in E133
whats special about that Row 133 , and what are you sorting?
it will change value when sorting
And change Cell reference when copying

 

[reberryjr]

Assuming the formula is on the summaries sheet use

=COUNTIFS('RESPONSE DETAILS'!$C:$C,$E133,'RESPONSE DETAILS'!$G:$G,"NI")

You should never refer to the sheet name the formula is in.

@Fluff thanks as always! As far as the reference to the sheet the formula is in, excel automatically enters that, so I guess I'll just need to start deleting those references.

 

[Fluff]

Glad we could help & thanks for the feedback.