How can I run SUMIF or COUNTIF with two Conditions?

I recently got some great help with `SUMIF` and `COUNTIF` and ended up with the following, working formulas;

``````=SUMIF(REC!\$F:\$F,\$C2,REC!\$G:\$G)
=COUNTIF(REC!\$F:\$F,\$C2)
``````

I had been mixing up the syntax and swapping the sum range with the criteria range.

Now I am trying to sum up and count the instances where a given vendor `F` is contracted by a location `Q`. Unfortunately, trying to add `-IFS` to these is not working out. My expectation was that the following would work;

``````=SUMIFS(REC!\$F:\$F,\$C2,REC!\$G:\$G,REC!\$Q:\$Q,\$G\$2)
=COUNTIFS(REC!\$F:\$F,\$C2,REC!\$Q:\$Q,\$G\$2)
``````

So,

• the SUMIFS formula is testing (`REC!\$F:\$F,\$C2,REC!\$G:\$G`) and (`REC!\$F:\$F,REC!\$Q:\$Q,\$G\$2`)
• the COUNTIFS formula is testing (`REC!\$Q:\$Q,\$G\$2`) and (`REC!\$F:\$F,\$C2`)

The REC! sheet is a listing of vendor projects with the following fields: `REC!F` column and the `C` column contain vendor IDs; `G,H` is fees, hours for each project; `REC!Q` is the column containing the designated location. Some of the rows on the REC! sheet are blank in the `F,G,H` columns.

Am I, yet again, bumbling the syntax in the `SUMIFS` and `COUNTIFS` forumlas? Is there a simpler way using `SUMPRODUCT`? Would I be better off importing to Access?

I have also tried using array style formulas,

``````{=SUMIF(REC!\$F:\$F,\$C5,REC!\$G:\$G)*AND(REC!\$Q:\$Q,\$G\$2)}
{=SUMIFS(REC!\$F:\$F,\$C2, REC!\$G:\$G, REC!\$Q:\$Q, \$G\$2)}
``````

But they all issue [#VALUE!] errors and have evaluations like [0,0,0,0,0....].

-

I can't see your data so take this with a grain of salt, but if you are entering the formulas as they appear above, the issue is the syntax - you're going to have to swap again :) `SUMIF` takes `range, criteria, sum_range`, while `SUMIFS` takes `sum_range, criteria_range1, criteria_1, etc.`. That being the case, try this for the `SUMIFS`:
``````=SUMIFS(REC!\$G:\$G,REC!\$F:\$F,\$C2,REC!\$Q:\$Q,\$G\$2)
This assumes that `REC!\$G:\$G` is what you want to sum. As for the `COUNTIFS`, what error are you getting? Your syntax looks correct.