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
)
Best Answer
Your syntax is wrong. For SUMIF you want the Range to be
Sheet1!A:A
, Criteria to beA1
(if that is where the single instance of the vendor ID# is held) and the Sum_range to beSheet1!B:B
.Example
Sheet2
(inside box) below where the formulae are the contents ofRow6
.