Countifs total

[Shamz41]

Hi
I would need some help with the count function on the attached sample. I need to count on worksheet2 tab, the number of tickets opened by john and mike based on the input in column J which I have highlighted in green. The count would have to also by seperated by site which is in column A.

I have the below formula which I just need help on the missing function to count by site, tickets opened by john and mike in cell C3 and to amend the same formula to count the tickets not opened by john and mike in cell C4

Please Login or Register  to view this content.

[AliGW]

You have not attached the workbook.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[FDibbins]

I think you forgot to attach the file?

[FDibbins]

looking again, have you tried to just include the extra criteria in the countifs?

[Shamz41]

Hmm have reattached the file.

[AliGW]

Try this in C3:

=SUM(COUNTIFS(worksheet1!$J:$J,{"Mike","John"},worksheet1!$B:$B,C$2,worksheet1!$A:$A,SUBSTITUTE(B$2," ","")))

and this in C4:

=SUM(COUNTIFS(worksheet1!$B:$B,C$2,worksheet1!$A:$A,SUBSTITUTE(B$2," ","")))-C3

[FDibbins]

edit: Ali beat me to it

[AliGW]

Sorry, Ford.

[FDibbins]

Not a problem, I was way late anyway lol

[Shamz41]

Hi this worked but I realised that if there is a 0 value in C3, the value in C4 turns negative if there is a count. Is there any way I can fix that.

[AliGW]

How can the count turn negative if you are subtracting zero from it??? Don't understand. However ...

How about this?

=MAX(0,SUM(COUNTIFS(worksheet1!$B:$B,C$2,worksheet1!$A:$A,SUBSTITUTE(B$2," ","")))-C3)

assuming you want the count to stop at zero.

[AliGW]

On reflection, maybe this is what you need?

=ABS(SUM(COUNTIFS(worksheet1!$B:$B,C$2,worksheet1!$A:$A,SUBSTITUTE(B$2," ","")))-C3)

[Shamz41]

That worked perfectly. Thank you.

[AliGW]

You're welcome - was it the second one that worked?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Shamz41]

Actually both formulas worked. Thank you. I've marked the thread as solved.

[AliGW]

Thanks for clarifying. Glad to have helped!

[JohnTopley]

Corrected: all solved apparently!

[Shamz41]

Hi Sry another road block. On my actual spreadsheet The reference value is located in another external file. So when i closed the file the formulas turned into error value #value. How would I amend the formula?

[AliGW]

You will need to keep the other file open. However, this really is a separate issue: the original query, as posed, has been solved here, so you should mark it as such and start a new thread for the new issue, which is related to the use of an external file.