Coutifs formula

[benjamin_1986]

Hi,

can you see the attached file and let me know if there is anything you can help with?

I am using this type of formula: =COUNTIFS(Sheet1!$E$2:$E$43225, A2,Sheet1!$F$2:$F$43225,"="&"OCT") but can report only one column.


Regards
TEST.xlsx

[Jonmo1]

For the question you actually asked
"I would like to know in column "code 1" sheet 2, how many times the value "1" appears in column RFT; RFT1:RFT 2;RFT3."
That would be
=COUNTIF(Sheet1!$A$2:$C$54,1)

But reading between the lines, You probably want
=SUMPRODUCT((Sheet1!$A$2:$D$54=1)*(Sheet1!$F$2:$F$54="Oct"))

[ChemistB]

You can use a SUMPRODUCT

Changing row 1 in sheet2 to A,1,2, In C2 copied across and down

=SUMPRODUCT((Sheet1!$E$2:$E$43225 =$A2)*(Sheet1!$F$2:$F$43225 = "OCT")* (Sheet1!$A$2:$D$43225= C$1))
Does that work for you?

[benjamin_1986]

Hi thanks, there formulas are great, But I don't get the exact results I'd like. With the formula: =COUNTIFS(Sheet1!$E$2:$E$43225, A2,Sheet1!$A$2:$A$43225,"="&"1") I get the results below, It calculates the number of A in column A. I just need to drag it down and it calculates for the other PTCs.

PTC "1"

APTC 22
EPTC 9
JPTC 6
LAPTC 0
NAPTC 0

Now what I want, it is a similar formula enabling me to take in account the "1" in the column B, C, and D as well. With the results above, I know that there is 22 "1" in the column A for APTC, but I need the formula to report the "1" in the Colum B, C, D as well.

Cheers