Counting number of values in a column against a text/numeral in 2 other columns.

[noor_ain]

I am trying to count number of times "Collected" appeared in Column C when Column A equals "Brick" and Column B has a non zero entity.

The return value cell is in another excel file.



A B C
Brick 1 Collected
Brick 4 Dispatched
Stone 6 Dispatched
Brick 0 Collected

The return value should be 1.

[cffndncr]

=countifs(A:A,"Brick",B:B,"<>0",C:C,"Collected")

Edit: Didn't see the 'other excel file' bit. Just change the column references to the other file. eg. instead of A:A, use '[workbook name]Sheetname'!A:A

[FDibbins]

cff, OP's profile indicates 2003 - the "ifS" functions only work from 2007 onwards, I think they will need to use sumproduct() instead. something like...
=sumproduct(((A2:A100="Brick")*(B2:B100>0)*(C2:C100="Collected"))
(untested)

[noor_ain]

Thankyou. It worked. The only problem is, that if I have the main file opened [containing Columns A B and C, Call it Data.xlsx], it gives me the value in the "other" file. [Call it "Summary.xlsx. But if I have opened only Summary.xlsx, then it won't give me the value.

[noor_ain]

Thankyou. This formula worked as well.

[FDibbins]

I notice that the countifS() seems to have worked for you. This indicates 2007 or later (.xlsx), but your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version