Forum Discussion
Solved
Formulas
We have a range where each categories has notes.
Please find the below condition where 1, 2 are the volumes.
CM- Account Credit (contl) category should be 1-Credit or 2-hold or 1-rejected and...
I'd also recommend to re-design right table a bit as
with Helper column calculated as
=TRIM(IFERROR( RIGHT( $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)), LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) - FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))), $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))Status could be calculated as
by formula
=IF(COUNTIFS( $G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)), $A2, $I$3:$I$9, IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2) ),"No Error", "Error")
SergeiBaklan - There is space after hyphen, so it is error.
| CM- Account Credit (contl) | 1- credit | Error |
I'd also recommend to re-design right table a bit as
with Helper column calculated as
=TRIM(IFERROR(
RIGHT(
$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)),
LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) -
FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))),
$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))
Status could be calculated as
by formula
=IF(COUNTIFS(
$G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)),
$A2,
$I$3:$I$9,
IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2)
),"No Error", "Error")Thank you it works for me.
resham1985 , you are welcome