2

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)

mfg
  • 627
  • 2
  • 8
  • 27
  • 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

1 Answers1

4

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

pnuts
  • 6,072
  • 3
  • 28
  • 41