Forum Discussion
Solved
Excel formula or chart?
Hi,
i tried countif but it didn't work. Can anyone help please?
Basically i have a list of delivery numbers that have materials against them. 1 delivery may have 1 material, some deliveries may have 2 materials etc I would like to use a formula to find me deliveries that just have 1 material so 1 line and i would like to see those delivery numbers - any idea? Thanks Rach
- Hi Quadruple_pawn,
Thank you so much this formula IT worked :). Is there any way to further enhance the formula by excluding route schedule PA2*_4850, PLT*_4850,INT*4850 from the results or would it need an additional formula? many thanks
4 Replies
=IF(SMALL(IF($A$2:$A$29=A2,ROW($A$2:$A$29)-1),1)=ROW()-1,COUNTIF($A$2:$A$29,A2),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Hi Quadruple_pawn,
Thank you so much this formula IT worked :). Is there any way to further enhance the formula by excluding route schedule PA2*_4850, PLT*_4850,INT*4850 from the results or would it need an additional formula? many thanks=IFERROR(IF(SMALL(IF(($A$2:$A$29=A2)*(NOT(ISNUMBER(SEARCH("PA2*_4850",$D$2:$D$29))))*(NOT(ISNUMBER(SEARCH("PLT*_4850",$D$2:$D$29))))*(NOT(ISNUMBER(SEARCH("INT*4850",$D$2:$D$29)))),ROW($A$2:$A$29)-1),1)=ROW()-1,SUMPRODUCT(($A$2:$A$29=A2)*(NOT(ISNUMBER(SEARCH("PA2*_4850",$D$2:$D$29))))*(NOT(ISNUMBER(SEARCH("PLT*_4850",$D$2:$D$29))))*(NOT(ISNUMBER(SEARCH("INT*4850",$D$2:$D$29))))),""),"")You are welcome. You can try this formula which has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021. I've changed the values in range D21:D26 in order to verify that the formula works as intended.
