SumIFs เป็นสูตรที่สามารถหายอดรวมตามเงื่อนไขได้หลายเงื่อนไข โดยมีข้อแม้ว่าเงื่อนไขเหล่านั้นต้องเป็นต่างเรื่องกัน เช่นใช้หายอดรวมของยอดขายสินค้ารหัสนั้น ชื่อลูกค้าคนนั้น ในวันที่นั้น แต่ไม่สามารถใช้กับเงื่อนไขที่เป็นเรื่องเดียวกัน เช่น ไม่สามารถใช้หายอดรวมของสินค้านั้นรวมกับสินค้านี้
ส่วน SumProduct สามารถใช้ได้โดยไม่มีปัญหาแต่อย่างใด โดยใช้หลักจำง่ายๆว่า
– ถ้าเงื่อนไขเป็นเรื่องเดียวกัน ให้นำเงื่อนไขมาบวกกัน และมีวงเล็บด้านหน้า 3 ชั้น
– ถ้าเงื่อนไขเป็นต่างเรื่องกัน ให้นำเงื่อนไขมาคูณกัน และมีวงเล็บด้านหน้า 2 ชั้น
นอกจากนี้ SumIFs ยังไม่สามารถนำสูตรอื่นมาซ้อนเข้าไปในวงเล็บ ส่วน SumProduct ซ้อนได้ตามสบาย
จากภาพนี้ถ้าไม่มีรายการซ้ำเลย สูตร SumIFS จะช่วยหาว่า ณ วันที่ 20/11/2019 มีรายการ a004 ที่ชื่อ d นั้นมี Amount เท่ากับ 40
=SumIFS(Amount, Date, 20/11/2019, Id, a004, Name, d)
แต่ถ้าต้องการใช้เงื่อนไขหาเฉพาะวันที่ 20 เท่านั้นล่ะ พอนำสูตร Day ซ้อนเข้าไปแบบนี้จะพบว่า Excel ไม่ยอมให้สร้างสูตรแบบที่ต้องการเข้าไปด้วยซ้ำ
=SumIFS(Amount, Day(Date), 20, Id, a004, Name, d)
ทำไมน่ะหรือ เพราะ SumIFS เป็นสูตรที่ไม่ยอมให้ซ้อนสูตรอื่นเข้าไปในวงเล็บ จึงเหมาะกับเงื่อนไขตรงกับข้อมูลชัดเจน ส่วนสูตร SumProduct ยอมรับการซ้อนสูตรจึงยืดหยุ่นกว่ามาก
=SumProduct((DAY(Date)=20)*(Id=a004)*(Name=d)*Amount)
หากต้องการหาว่ารายการที่ต้องการนั้นอยู่ที่ row ใด ก็เพียงซ้อนสูตร Row เข้าไปแบบนี้
=SumProduct((DAY(Date)=20)*(Id=a004)*(Name=d)*Row(Amount))
จากเลขที่ Row ที่หาได้ พอนำไปใช้บอกตำแหน่งในสูตร Index จะช่วยหาข้อมูลของรายการนั้นที่เป็น Text หรือตัวอักษรได้โดยไม่จำกัดว่าต้องเป็นตัวเลขเท่านั้นอีกต่อไป
=Index (Column ของข้อมูลเรื่องที่ต้องการ, เลขที่ Row)
นอกจากนี้สูตร SumIFS ยังรับเงื่อนไขได้เฉพาะแบบ And เท่านั้น หากจะหายอดรวมของรหัสนั้นหรือรหัสนี้ซึ่งใช้แบบ Or จะทำไม่ได้ ส่วน SumProduct ทำได้สบายมาก
*** ถ้าค่าที่ต้องการหาเป็นตัวเลข และมีเพียงรายการเดียว ไม่มีซ้ำ ให้ใช้ SumProduct หาได้เลยครับ
โยนสูตร VLookup Match Index ทิ้งไป
ที่มา
หลักสูตรเรื่องรีบรู้เพื่อพร้อมใช้ Excel ทำงานแบบ Fast and Easy
https://www.excelexperttraining.com/online/courses/05-fast-and-easy/