Excel formula or chart?

[rach1345]

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

[#2]

@rach1345 

=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.

 

[#3 · Accepted solution]

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]

@rach1345 

=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.

[#5]

Thank you so much I dont know how you do it :). I will try and put a like on this afterwards as well :)