Ask Your Question

# How to Sum only if range totals greater than 3 [closed] hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.

If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.

I hope this makes sense, any advice is appreciately very much.

edit retag reopen merge delete

## 1 Answer

Sort by » oldest newest most voted If I have understood your question, next a couple of formulas to do it:

=SUMPRODUCT($A$2:$A$6;$B$2:$B$6;$C$2:$C$6)

=COUNTIFS($A$2:$A$6;1;$B$2:$B$6;1;$C$2:$C$6;1)

attached a sample file, CountWithSeveralConditions.ods

There are more options but I think they implies to be introduced as arrays what sometimes is not so easy, while SUMPRODUCT() even working as array doesn't need to be enter as such.

more

## Comments

Thank you for replying, I tried using your example above but not completely understanding it. (Tried to open the file you sent but it isnt opening correctly, and not sure which file there are many xml files inside it, no osd files)

But to clarify what I would like to do is make it so if 3 cells in a row have a number one in them, then count that row as 1. for example =If K16:N16 is equal to or greater than 3 total, then count this row as value of one. And each row will have same formula.

## Stats

Asked: 2014-07-22 20:57:33 +0200

Seen: 569 times

Last updated: Jul 23 '14