Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am building a simple sum sheet that relies on a conditional but I can't seem to get COUNTIF/SUMIF to work for me.

To sum, I have two sheets, one with a few hundred lines of records including an vendor ID# (Sheet1|2!A:A), fees (B:B), and hours (all of which may be duplicate, sorted by vendor ID#). On the second sheet, I have taken the vendor ID#s, removed duplicates, and would like the remainder of the sheet be a summation of the records from the first sheet.

Specifically, I was expecting a formula something like this to work:

  • =SUMIF(Sheet1!B:B, Sheet1!A:A=Sheet2!A:A) (to find total fees, hours)

  • =COUNTIF(Sheet1!A:A, Sheet1!A:A=Sheet2!A:A) (to find frequency of vendor ID in rows)

Both of these are only returning zero once they're entered, however. Basically, I want it to sum the fees column (B:B) from the first sheet on the second sheet, wherever the logical test comes back true that the vendor ID on sheet 1 is the same as the vendor ID on sheet 2 (...1!A=...2!A)

share|improve this question
I have verified that the equivalency could work (i.e. the data is stored correctly and can return a TRUE in 1!A2=2!A2); I seem to be incorrectly inputting the series into the test – mfg Sep 24 '12 at 14:47
up vote 4 down vote accepted

Your syntax is wrong. For SUMIF you want the Range to be Sheet1!A:A, Criteria to be A1 (if that is where the single instance of the vendor ID# is held) and the Sum_range to be Sheet1!B:B.

Example Sheet2 (inside box) below where the formulae are the contents of Row6.

enter image description here

share|improve this answer

Your Answer


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.