Counting cells in on column minus cell from anohter

[rgirardi]

Hi
I am trying write a formula that will count the cell in column "J" with certain words total them and then minus the number of cells in column ''E" with a certain word. This is my formula but it does not come up with the correct total.
=COUNTIF(Table!J:J,"Pending Closure")+COUNTIF(Table!J:J,"Pending Report Response")+COUNTIF(Table!J:J,"Closed - Done")-COUNTIF(Table!E:E,"Cancel")

It should come up with 14.

The words in column "J" add to 16 and then it should minus the number of times the word "Cancel" appears in column "E" which is 2, so I should get a total of 14. The formula returns the number 3.

Thanks
Ralph

[etaf]

can we see a copy of the spreadsheet with any personal data removed

any spaces before or after the text in the columns which may be giving you an incorrect result

[rgirardi]

Hi sure I have attached it

[etaf]

i get 3

COUNTIF(J:J,"Pending Closure")
COUNTIF(J:J,"Pending Report Response")
COUNTIF(J:J,"Closed - Done")
COUNTIF(E:E,"Cancel")

2
2
12
-13

Total 3

I get the 16
BUT E has more than 2 Cancel

OR do you only want Cancel in E - when the other conditions are true

[rgirardi]

Hi
Sorry, I should have said that. I only want to subtract Cancel when the other conditions are true. So it should look like
2
2
12
-2
Total 14

[etaf]

you will probably need to use a COUNTIFS()

And Subtract each one of these

COUNTIFS(J:J,"Pending Closure",E:E,"Cancel")
COUNTIFS(J:J,"Pending Report Response",E:E,"Cancel")
COUNTIFS(J:J,"Closed - Done",E:E,"Cancel")

there maybe a better way using sumproduct or arrays
so will need to look into that

[rgirardi]

Sorry that did not work.

[etaf]

works for me
=COUNTIF(J:J,"Pending Closure")
+COUNTIF(J:J,"Pending Report Response")
+COUNTIF(J:J,"Closed - Done")
-COUNTIFS(J:J,"Pending Closure",E:E,"Cancel")
-COUNTIFS(J:J,"Pending Report Response",E:E,"Cancel")
-COUNTIFS(J:J,"Closed - Done",E:E,"Cancel")

[etaf]

Alternative
=SUM(COUNTIF(J:J,{"Pending Closure","Pending Report Response","Closed - Done"}))-SUM(COUNTIFS(J:J,{"Pending Closure","Pending Report Response","Closed - Done"},E:E,"cancel"))

[rgirardi]

Hi it did work, thank you.

[etaf]

you are welcome, thanks for the rep