Microsoft Excel 2007 – How to Use COUNTIF and SUMIF Across Two Sheets

countifmicrosoft-excel-2007worksheet-function

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

Related Question