0

this question may be repeated from enter link description here but the vales in my situation are alpha numeric. I would like to find number of unique materials that I received in specific calendar week with specific type.

Attached is the image. Kindly provide your suggestion.Thanksenter image description here

SriLaks
  • 137
  • 9

1 Answers1

1

If I understood your problem correctly then following formula should work for you:

=SUM(IF(((MATCH($A$2:$A$8&$B$2:$B$8&$C$2:$C$8,$A$2:$A$8&$B$2:$B$8&$C$2:$C$8,0))>=(ROW($A$2:$A$8)-(MIN(ROW($A$2:$A$8))-1)))*(($C$2:$C$8=$E2)*(($A$2:$A$8)=F$1))=1,1,0))

This is an array formula so commit it by pressing Ctrl+Shift+Enter. Drag/Copy down and across as required.

See image for reference.

enter image description here

Mrig
  • 11,612
  • 2
  • 13
  • 27
  • I used the above mention formula, it gives me a error #VALUE. Due to "," used instead of ";". I changed it to ";" still i get an error #VALUE. I am using excel 2013. – SriLaks Jun 29 '17 at 13:41
  • @SriLaks - This is an **Array formula** so instead of just pressing `Enter`, you have to press `Ctrl + Shift + Enter` to complete the formula. After formula is successfully entered you should be able to see curly brackets `{ }` around your formula. – Mrig Jun 30 '17 at 04:40