Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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....].

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for clearing that up! The COUNTIFS was correct, it was referring to a null set and wasn't returning a value, but I couldn't tell until I had the SUMIFS working. –  mfg Oct 2 '12 at 19:53
    
No prob! Happy it helped (I'd be lying if I said that syntax shift hadn't messed me up a few times as well). –  RocketDonkey Oct 2 '12 at 19:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.