Trying to count number of fruits I have in column G, I have:

=COUNTIF(G:G,"Oranges") and =COUNTIF(G:G,"Mangoes")

How do I create a formula to calculate the rest? I mean all other fruits apart from Mangoes and Oranges in column G?

4

2 Answers

This solves the issue:

• No need to use any extra column as Helper data used by @Erin.

• Formula in cell R2:

  =COUNTIFS($O$2:$O$15,"<>Oranges",$O$2:$O$15,"<>Mangoes")

• Formula in cell S2:

  =SUMPRODUCT(--($O$2:$O$15<>"Oranges"),--($O$2:$O$15<>"Mangoes"))

N.B.

• To make formula dynamic instead of values you may use cell references.

  =SUMPRODUCT(--($O$2:$O$15<>$P$2),--($O$2:$O$15<>$P$3))

• Where P2 & P3 comprises Oranges and Mangoes.

• Adjust cell references in the formula as needed.

1

Add a column all 1 in it.

Then use =SUMIFS(F:F,G:G,"<>Oranges",G:G,"<>Mangoes")

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy