Some data:
A B C D E
1 Tag1 Tag2 Tag3 Type Value
2 foo bar baz 1 1
3 foo bar bar 1 2
4 foo foo baz 2 1
5 foo bar baz 1 1
6 foo baz baz 1 2
7 foo bar foo 3 1
8 baz bar baz 1 3
9 foo bar baz 1 1
To count all "foo" in columns A, B and C:
=COUNTIFS($A:$C,"=foo")
To count all "foo" in column A, with restrictions:
=COUNTIFS($A:$A,"=foo", $D:$D,"=1", $E:$E,"=2")
However one can't do both: count all "foo" in columns A, B and C, with restrictions:
=COUNTIFS($A:$C,"=foo", $D:$D,"=1", $E:$E,"=2") # Err:502 (in LibreOffice Calc)
This does work:
=COUNTIFS($A:$A,"=foo", $D:$D,"=1", $E:$E,"=2") +
COUNTIFS($B:$B,"=foo", $D:$D,"=1", $E:$E,"=2") +
COUNTIFS($C:$C,"=foo", $D:$D,"=1", $E:$E,"=2")
However this is obviously a contrived example, so in my real spreadsheet that formula is a mess that's hard to maintain.
How does one do this?
(I'm using LibreOffice-Calc, but a solution in Excel would be fine too.)
you can also use SUMPRODUCT() function to achieve this result.
=SUMPRODUCT((A2:C9="foo")*(D2:D9=1)*(E2:E9=2))
output = 2.0