SUM not returning the value

[Stanman]

Hi,

I had the countIFS as a separate table and decided to try and see if I could add the IF to it to remove an additional step. It worked but messed up the sum, which was showing the right number but now returns a zero. I have formatted the cell to number but still no joy.

The COUNTIF is counting how many times a text occurs and the 1 is to remove duplicate counts.

The formula is =IF(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$P$1)>=1,"1",(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$P$1)))

Thank you

DOH DOH, sorry ignore fixed Had "1" around the returned value and counting it as text.

 

[Muhammad_Usman]

Hey,
Try this one

IF(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$P$1)">=1","1",(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$P$1)))

 

[Stanman]

Fixed it the issue was enclosing the return value in " " and that was treating it as text

=IF(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$D$1)>=1,1,(COUNTIFS(Data_Sheet!$F$2:$F$4000,A2,Data_Sheet!$C$2:$C$4000,$D$1)))