I am trying to count the instances of a filter in one columb and matching them with those of another column to get a total count For example, I have a column named IO type and another named power. I need to get a count for a type of IO (discrete input) which uses a specific power (24VC)
In this case if is the IO type “discrete in” and the power being “24VDC” since there are many rows with different power.
I have tried many ways to combine COUNTIF and the AND function but my syntax is always completely wrong.
does anyone know the correct syntax to combine these two and get a count that matches the desired criteria on two columns?
=COUNTIFS(D3:D18;"24 VDC"; F3:F18; "Discrete in")
Lol, right, I forgot about the most obvious choice `:)
=SUMPRODUCT(D3:D18="24 VDC";F3:F18="Discrete in")
Another way (though also more complicated if not used to) for more flexibility in setting up criteria would be to use DCOUNTA() with filter criteria. See also general explanation of Database Functions .
For this example, in I8:J9 (arbitrary criteria example range) enter
| I | J | |
|---|---|---|
| 8 | Power | IO Type |
| 9 | 24 VDC | Discrete in |
and somewhere the formula
=DCOUNTA(C2:G18;D2;I8:J9)