บทแนะนำนี้ให้การสาธิตโดยละเอียดเกี่ยวกับวิธีใช้ฟังก์ชัน SUMIF และ SUMIFS ใน Google ชีตพร้อมสูตรและตัวอย่าง
SUMIF เป็นหนึ่งในฟังก์ชันทางคณิตศาสตร์ใน Google ชีต ซึ่งใช้เพื่อรวมเซลล์แบบมีเงื่อนไข โดยทั่วไป ฟังก์ชัน SUMIF จะค้นหาเงื่อนไขเฉพาะในช่วงของเซลล์ แล้วบวกค่าที่ตรงตามเงื่อนไขที่กำหนด
ตัวอย่างเช่น คุณมีรายการค่าใช้จ่ายใน Google ชีต และคุณต้องการสรุปเฉพาะค่าใช้จ่ายที่สูงกว่าค่าสูงสุดที่กำหนดเท่านั้น หรือคุณมีรายการสั่งซื้อและจำนวนเงินที่เกี่ยวข้อง และคุณต้องการทราบเฉพาะยอดสั่งซื้อรวมของสินค้าเฉพาะ นั่นคือเหตุผลที่ฟังก์ชัน SUMIF มีประโยชน์
สามารถใช้ SUMIF เพื่อรวมค่าตามเงื่อนไขตัวเลข เงื่อนไขข้อความ เงื่อนไขวันที่ อักขระตัวแทน เช่นเดียวกับเซลล์ว่างและไม่ว่าง Google ชีตมีสองฟังก์ชันในการสรุปค่าตามเกณฑ์: SUMIF และ SUMIFS ฟังก์ชัน SUMIF จะเพิ่มตัวเลขโดยอิงตามเงื่อนไขเดียว ขณะที่ SUMIFS จะรวมตัวเลขตามหลายเงื่อนไข
ในบทแนะนำนี้ เราจะอธิบายวิธีใช้ฟังก์ชัน SUMIF และ SUMIFS ใน Google ชีตเพื่อรวมตัวเลขที่ตรงตามเงื่อนไขบางประการ
ฟังก์ชัน SUMIF ใน Google ชีต – ไวยากรณ์และอาร์กิวเมนต์
ฟังก์ชัน SUMIF เป็นเพียงการรวมกันของฟังก์ชัน SUM และ IF ฟังก์ชัน IF จะสแกนผ่านช่วงของเซลล์เพื่อหาเงื่อนไขที่กำหนด จากนั้นฟังก์ชัน SUM จะรวมตัวเลขที่สอดคล้องกับเซลล์ที่ตรงตามเงื่อนไข
รูปแบบของฟังก์ชัน SUMIF:
ไวยากรณ์ของฟังก์ชัน SUMIF ใน Google ชีตมีดังนี้:
=SUMIF(ช่วง เกณฑ์ [sum_range])ข้อโต้แย้ง:
ช่วง – ช่วงของเซลล์ที่เราค้นหาเซลล์ที่ตรงตามเกณฑ์
เกณฑ์ – เกณฑ์ที่กำหนดว่าต้องเพิ่มเซลล์ใด คุณสามารถยึดเกณฑ์ตามตัวเลข สตริงข้อความ วันที่ การอ้างอิงเซลล์ นิพจน์ ตัวดำเนินการทางตรรกะ อักขระตัวแทน ตลอดจนฟังก์ชันอื่นๆ
sum_range – อาร์กิวเมนต์นี้เป็นทางเลือก เป็นช่วงข้อมูลที่มีค่าที่จะรวมถ้ารายการช่วงที่สอดคล้องกันตรงกับเงื่อนไข หากคุณไม่ใส่อาร์กิวเมนต์นี้ ระบบจะรวม”ช่วง”แทน
ตอนนี้ เรามาดูวิธีใช้ฟังก์ชัน SUMIF เพื่อรวมค่าด้วยเกณฑ์ที่แตกต่างกัน
ฟังก์ชัน SUMIF พร้อมเกณฑ์ตัวเลข
คุณรวมตัวเลขที่ตรงกับเกณฑ์บางอย่างในช่วงเซลล์ได้โดยใช้ตัวดำเนินการเปรียบเทียบตัวใดตัวหนึ่งต่อไปนี้เพื่อสร้างเกณฑ์
- มากกว่า (>)
- น้อยกว่า (<)
- มากกว่าหรือเท่ากับ (>=)
- น้อยกว่าหรือเท่ากับ เป็น (<=)
- เท่ากับ (=)
- ไม่เท่ากับ (<>)
สมมติว่าคุณมีสเปรดชีตต่อไปนี้และคุณสนใจในยอดขายรวมที่ 1,000 ขึ้นไป
วิธีเข้าสู่ฟังก์ชัน SUMIF มีดังนี้
ขั้นแรก เลือกเซลล์ที่คุณต้องการให้ผลลัพธ์ของผลรวมปรากฏ (D3) ในการสรุปตัวเลขใน B2:B12 ที่มากกว่าหรือเท่ากับ 1,000 ให้พิมพ์สูตรนี้แล้วกด’Enter’:
=SUMIF(B2:B12,">=1000",B2:B12)ในสูตรตัวอย่างนี้ อาร์กิวเมนต์ range และ sum_range (B2:B12) จะเหมือนกัน เนื่องจากมีการใช้ตัวเลขและเกณฑ์การขายในช่วงเดียวกัน และเราป้อนตัวเลขก่อนตัวดำเนินการเปรียบเทียบและใส่ไว้ในเครื่องหมายคำพูด เนื่องจากเกณฑ์ควรอยู่ในเครื่องหมายคำพูดคู่เสมอ ยกเว้นสำหรับการอ้างอิงเซลล์
สูตรจะค้นหาตัวเลขที่มากกว่าหรือเท่ากับ 1000 แล้วบวกค่าที่ตรงกันทั้งหมดและแสดงผลลัพธ์ในเซลล์ D3
เนื่องจากอาร์กิวเมนต์ range และ sum_range เหมือนกัน คุณสามารถบรรลุผลลัพธ์เดียวกันได้โดยไม่ต้องใช้อาร์กิวเมนต์ sum_range ในสูตร ดังนี้:
=SUMIF(B2:B12,">=1000")
หรือคุณสามารถระบุการอ้างอิงเซลล์ (D2) ที่มีตัวเลขแทนที่จะเป็นเกณฑ์ตัวเลข และรวมตัวดำเนินการเปรียบเทียบกับการอ้างอิงเซลล์นั้นในอาร์กิวเมนต์เกณฑ์:
=SUMIF(B2:B12,">="&D2)ดังที่คุณเห็นว่าตัวดำเนินการเปรียบเทียบยังคงถูกป้อนในเครื่องหมายคำพูดคู่ และตัวดำเนินการและการอ้างอิงเซลล์จะถูกเชื่อมด้วยเครื่องหมายและ (&) และคุณไม่จำเป็นต้องใส่การอ้างอิงเซลล์ในเครื่องหมายคำพูด
หมายเหตุ: เมื่อคุณอ้างถึงเซลล์ที่มีเกณฑ์ ตรวจสอบให้แน่ใจว่าไม่ได้เว้นช่องว่างนำหน้าหรือต่อท้ายในค่าใน เซลล์ หากค่าของคุณมีช่องว่างที่ไม่จำเป็นก่อนหรือหลังค่าในเซลล์ที่อ้างอิง สูตรจะคืนค่าเป็น’0′
คุณยังสามารถใช้ตัวดำเนินการทางตรรกะอื่นๆ ในลักษณะเดียวกันเพื่อสร้างเงื่อนไขในอาร์กิวเมนต์เกณฑ์ ตัวอย่างเช่น ในการรวมค่าที่น้อยกว่า 500:
=SUMIF(B2:B12,"<500")
ผลรวมถ้าตัวเลขเท่ากับ
ถ้าคุณต้องการเพิ่มตัวเลขที่เท่ากับจำนวนหนึ่ง คุณสามารถป้อนเฉพาะตัวเลขหรือป้อนตัวเลขที่มีเครื่องหมายเท่ากับในอาร์กิวเมนต์เกณฑ์
ตัวอย่างเช่น หากต้องการรวมยอดขายที่สอดคล้องกัน (คอลัมน์ B) สำหรับปริมาณ (คอลัมน์ C) ที่มีค่าเท่ากับ 20 ให้ลองใช้สูตรเหล่านี้:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
ในการรวมตัวเลขในคอลัมน์ B ที่มีปริมาณไม่เท่ากับ 20 ในคอลัมน์ C ให้ลองใช้สูตรนี้:
=SUMIF(C2:C12,"<>20",B2:B12)
ฟังก์ชัน SUMIF พร้อมเกณฑ์ข้อความ
ถ้าคุณต้องการเพิ่มตัวเลขในช่วงเซลล์ (คอลัมน์หรือแถว) ที่สอดคล้องกับเซลล์ที่มีข้อความเฉพาะ คุณสามารถรวมข้อความนั้นหรือเซลล์ที่มีข้อความในอาร์กิวเมนต์เกณฑ์ของสูตร SUMIF ได้. โปรดทราบว่าสตริงข้อความควรอยู่ในเครื่องหมายคำพูดคู่ (” “) เสมอ
ตัวอย่างเช่น หากคุณต้องการยอดขายทั้งหมดในภูมิภาค'ตะวันตก'คุณสามารถใช้สูตรด้านล่าง:
=SUMIF(C2:C13,"West",B2:B13)ในสูตรนี้ ฟังก์ชัน SUMIF จะค้นหาค่า'West'ในช่วงเซลล์ C2:C13 และเพิ่มมูลค่าการขายที่สอดคล้องกันในคอลัมน์ B จากนั้นแสดงผลลัพธ์ในเซลล์ E3
คุณยังสามารถอ้างถึงเซลล์ที่มีข้อความแทนการใช้ข้อความในอาร์กิวเมนต์เกณฑ์ได้:
=SUMIF(C2:C12,E2,B2:B12)
ตอนนี้ มาดูรายได้รวมของทุกภูมิภาคยกเว้น"ตะวันตก"ในการทำเช่นนั้น เราจะใช้ตัวดำเนินการไม่เท่ากับ (<>) ในสูตร:
=SUMIF(C2:C12,"<>"&E2,B2:B12)
SUMIF พร้อมไวด์การ์ด
ในวิธีการข้างต้น ฟังก์ชัน SUMIF พร้อมเกณฑ์ข้อความจะตรวจสอบช่วงกับข้อความที่ระบุ จากนั้นจะรวมตัวเลขที่เรียงกันเป็นข้อความที่ถูกต้องและละเว้นตัวเลขอื่นๆ ทั้งหมด รวมทั้งสตริงข้อความที่ตรงกันบางส่วน ในการรวมตัวเลขที่มีสตริงข้อความที่ตรงกันบางส่วน คุณต้องปรับแต่งหนึ่งในอักขระตัวแทนต่อไปนี้ในเกณฑ์ของคุณ:
?(เครื่องหมายคำถาม) ใช้เพื่อจับคู่อักขระตัวเดียวที่ใดก็ได้ในสตริงข้อความ*(ดอกจัน) ใช้เพื่อค้นหาคำที่ตรงกันพร้อมกับลำดับอักขระ~(ตัวหนอน) ใช้เพื่อจับคู่ข้อความด้วยเครื่องหมายคำถาม (?) หรือเครื่องหมายดอกจัน (*)
เราจะยกตัวอย่างสเปรดชีตสำหรับผลิตภัณฑ์และปริมาณของผลิตภัณฑ์เพื่อรวมตัวเลขด้วยสัญลักษณ์แทน:
ดอกจัน (*) Wildcard
ตัวอย่างเช่น หากคุณต้องการรวมปริมาณของผลิตภัณฑ์ Apple ทั้งหมด ให้ใช้สูตรนี้:
=SUMIF(A2:A14,"Apple*",B2:B14)สูตร SUMIF นี้จะค้นหาผลิตภัณฑ์ทั้งหมดที่มีคำว่า"Apple"ขึ้นต้นและตามด้วยอักขระจำนวนเท่าใดก็ได้ (แสดงด้วย'*') เมื่อพบการจับคู่แล้ว ระบบจะสรุปตัวเลข จำนวน ที่ตรงกับสตริงข้อความที่ตรงกัน
นอกจากนี้ยังสามารถใช้สัญลักษณ์แทนหลายตัวในเกณฑ์ได้อีกด้วย และคุณยังป้อนอักขระตัวแทนด้วยการอ้างอิงเซลล์แทนข้อความโดยตรงได้อีกด้วย
ในการทำเช่นนั้น ไวด์การ์ดจะต้องอยู่ในเครื่องหมายอัญประกาศคู่ (“ “) และต่อด้วยการอ้างอิงเซลล์:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)สูตรนี้รวมปริมาณของผลิตภัณฑ์ทั้งหมดที่มีคำว่า'Redmi'อยู่ในนั้น ไม่ว่าคำนั้นจะอยู่ที่ใดในสตริง
เครื่องหมายคำถาม (?) Wildcard
คุณสามารถใช้สัญลักษณ์แทนเครื่องหมายคำถาม (?) เพื่อจับคู่สตริงข้อความกับอักขระตัวเดียวได้
ตัวอย่างเช่น หากคุณต้องการค้นหาปริมาณของ Xiaomi Redmi 9 ทุกรุ่น คุณสามารถใช้สูตรนี้:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)สูตรข้างต้นจะค้นหาสตริงข้อความที่มีคำว่า"Xiaomi Redmi 9"ตามด้วยอักขระเดี่ยวใดๆ และรวมตัวเลข จำนวน ที่เกี่ยวข้อง
ตัวหนอน (~) Wildcard
หากคุณต้องการจับคู่เครื่องหมายคำถามจริง (?) หรือเครื่องหมายดอกจัน (*) ให้ใส่อักขระตัวหนอน (~) ก่อนสัญลักษณ์แทนในส่วนเงื่อนไขของสูตร
หากต้องการเพิ่มปริมาณในคอลัมน์ B ด้วยสตริงที่เกี่ยวข้องที่มีเครื่องหมายดอกจันที่ท้าย ให้ป้อนสูตรด้านล่าง:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
หากต้องการเพิ่มปริมาณในคอลัมน์ B ที่มีเครื่องหมายคำถาม (?) ในคอลัมน์ A ในแถวเดียวกัน ให้ลองใช้สูตรด้านล่าง:
=SUMIF(A2:A14,"~?",B2:B14)
ฟังก์ชัน SUMIF พร้อมเกณฑ์วันที่
ฟังก์ชัน SUMIF ยังช่วยให้คุณรวมค่าตามเงื่อนไขตามเกณฑ์วันที่ได้ เช่น ตัวเลขที่สัมพันธ์กับวันที่ที่ระบุ หรือก่อนวันที่ หรือหลังวันที่ คุณยังสามารถใช้ตัวดำเนินการเปรียบเทียบใดๆ ที่มีค่าวันที่เพื่อสร้างเกณฑ์วันที่สำหรับการรวมตัวเลขได้
ต้องป้อนวันที่ในรูปแบบวันที่ที่สนับสนุนใน Google ชีต หรือเป็นการอ้างอิงเซลล์ที่มีวันที่ หรือใช้ฟังก์ชันวันที่ เช่น DATE() หรือ TODAY()
เราจะใช้สเปรดชีตตัวอย่างนี้เพื่อแสดงให้คุณเห็นว่าฟังก์ชัน SUMIF พร้อมเกณฑ์วันที่ทำงานอย่างไร:
สมมติว่าคุณต้องการรวมยอดขายที่เกิดขึ้นในหรือก่อนหน้านั้น (<=) 29 พฤศจิกายน 2019 ในชุดข้อมูลด้านบน คุณสามารถเพิ่มตัวเลขการขายเหล่านั้นได้โดยใช้ฟังก์ชัน SUMIF ด้วยวิธีใดวิธีหนึ่งต่อไปนี้:
=SUMIF(C2:C13,"<=November 29, 2019",B2:B13)สูตรข้างต้นจะตรวจสอบแต่ละเซลล์ตั้งแต่ C2 ถึง C13 และจับคู่เฉพาะเซลล์ที่มีวันที่ในหรือก่อนวันที่ 29 พฤศจิกายน 2019 (29/11/2019) จากนั้นจึงรวมยอดขายที่สอดคล้องกับเซลล์ที่ตรงกันจากช่วงเซลล์ B2:B13 และแสดงผลลัพธ์ในเซลล์ E3
ระบุวันที่ในสูตรในรูปแบบใดก็ได้ที่ Google ชีตรู้จัก เช่น"29 พฤศจิกายน 2019""29 พฤศจิกายน 2019"หรือ"29/11/2019"เป็นต้น โปรดจำค่าวันที่และ ตัวดำเนินการต้องอยู่ในเครื่องหมายคำพูดคู่เสมอ
คุณยังสามารถใช้ฟังก์ชัน DATE() ในเกณฑ์แทนการกำหนดวันที่โดยตรง:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
หรือคุณสามารถใช้การอ้างอิงเซลล์แทนวันที่ในส่วนเกณฑ์ของสูตร:
=SUMIF(C2:C13,"<="&E2,B2:B13)
ถ้าคุณต้องการเพิ่มยอดขายร่วมกันตามวันที่ของวันนี้ คุณสามารถใช้ฟังก์ชัน TODAY() ในอาร์กิวเมนต์เกณฑ์ได้
ตัวอย่างเช่น หากต้องการรวมยอดขายทั้งหมดสำหรับวันที่ของวันนี้ ให้ใช้สูตรนี้:
=SUMIF(C2:C13,TODAY(),B2:B13)
ฟังก์ชัน SUMIF พร้อมเซลล์ว่างหรือไม่ว่าง
บางครั้ง คุณอาจต้องรวมตัวเลขในช่วงของเซลล์ที่มีเซลล์ว่างหรือไม่ว่างในแถวเดียวกัน ในกรณีดังกล่าว คุณสามารถใช้ฟังก์ชัน SUMIF เพื่อรวมค่าตามเกณฑ์ที่เซลล์ว่างหรือไม่
ผลรวมถ้าว่าง
มีเกณฑ์ 2 ข้อใน Google ชีตเพื่อค้นหาเซลล์ว่าง: “” หรือ “=”
ตัวอย่างเช่น หากคุณต้องการรวมยอดขายทั้งหมดที่มีสตริงที่มีความยาวเป็นศูนย์ (ดูเหมือนว่างเปล่า) ในคอลัมน์ C ให้ใช้เครื่องหมายอัญประกาศคู่โดยไม่มีช่องว่างระหว่างในสูตร:
=SUMIF(C2:C13,"",B2:B13)
หากต้องการรวมยอดขายทั้งหมดในคอลัมน์ B ที่มีเซลล์ว่างในคอลัมน์ C ให้ใส่"="เป็นเกณฑ์:
=SUMIF(C2:C13,"=",B2:B13)
ผลรวมหากไม่เว้น:
ถ้าคุณต้องการรวมเซลล์ที่มีค่าใดๆ (ไม่ว่าง) คุณสามารถใช้ “<>” เป็นเกณฑ์ในสูตรได้:
ตัวอย่างเช่น หากต้องการทราบยอดรวมของยอดขายพร้อมวันที่ใดๆ ให้ใช้สูตรนี้:
=SUMIF(C2:C13,"<>",B2:B13)
SUMIF อิงตามเกณฑ์หลายเกณฑ์ที่มี OR Logic
ดังที่เราได้เห็นมาจนถึงตอนนี้ ฟังก์ชัน SUMIF ได้รับการออกแบบมาเพื่อรวมตัวเลขตามเกณฑ์เพียงเกณฑ์เดียว แต่สามารถรวมค่าตามเกณฑ์หลายเกณฑ์ด้วยฟังก์ชัน SUMIF ใน Google ชีตได้ สามารถทำได้โดยการรวมฟังก์ชัน SUMIF มากกว่าหนึ่งฟังก์ชันในสูตรเดียวกับตรรกะ OR
ตัวอย่างเช่น หากคุณต้องการสรุปยอดขายในภูมิภาค"ตะวันตก"หรือ"ใต้"(ตรรกะ OR) ในช่วงที่ระบุ (B2:B13) ให้ใช้สูตรนี้:
=SUMIF(C2:C13,"West",B2:B13)+SUMIF(C2:C13,"South",B2:B13)สูตรนี้จะรวมเซลล์เมื่อมีเงื่อนไขอย่างน้อยหนึ่งข้อที่เป็น TRUE ดังนั้นจึงเรียกว่า'OR ตรรกะ'นอกจากนี้ยังจะรวมค่าเมื่อตรงตามเงื่อนไขทั้งหมด
ส่วนแรกของสูตรจะตรวจสอบช่วง C2:C13 สำหรับข้อความ"ตะวันตก"และรวมค่าในช่วง B2:B13 เมื่อตรงกับค่าที่ตรงกัน ส่วนวินาทีของการตรวจสอบค่าข้อความ'South'ในช่วงเดียวกัน C2:C13 จากนั้นรวมค่าที่มีข้อความที่ตรงกันใน sum_range B2:B13 เดียวกัน จากนั้นผลรวมทั้งสองจะถูกรวมเข้าด้วยกันและแสดงในเซลล์ E3
ในกรณีที่ตรงตามเกณฑ์เพียงข้อเดียว ระบบจะส่งกลับค่าผลรวมนั้นเท่านั้น
คุณยังสามารถใช้เกณฑ์ได้หลายเกณฑ์ แทนที่จะใช้เพียงหนึ่งหรือสองเกณฑ์ และถ้าคุณใช้หลายเกณฑ์ ควรใช้การอ้างอิงเซลล์เป็นเกณฑ์แทนการเขียนค่าโดยตรงในสูตร
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4, B2:B13)
SUMIF ที่มีตรรกะ OR จะเพิ่มค่าเมื่อตรงตามเกณฑ์ที่ระบุอย่างน้อยหนึ่งรายการ แต่ถ้าคุณต้องการรวมค่าเฉพาะเมื่อตรงตามเงื่อนไขที่ระบุทั้งหมดเท่านั้น คุณต้องใช้ฟังก์ชัน SUMIFS() ที่เป็นพี่น้องกันใหม่/p>
ฟังก์ชัน SUMIFS ใน Google ชีต (หลายเกณฑ์)
เมื่อคุณใช้ฟังก์ชัน SUMIF เพื่อรวมค่าตามเกณฑ์หลายเกณฑ์ สูตรอาจยาวเกินไปและซับซ้อนเกินไป และคุณมีแนวโน้มที่จะทำผิดพลาด นอกจากนี้ SUMIF ยังให้คุณรวมค่าในช่วงเดียวและเมื่อเงื่อนไขข้อใดข้อหนึ่งเป็น TRUE นั่นคือที่มาของฟังก์ชัน SUMIFS
ฟังก์ชัน SUMIFS ช่วยให้คุณรวมค่าตามเกณฑ์ที่ตรงกันหลายรายการในช่วงตั้งแต่หนึ่งช่วงขึ้นไป และทำงานบนตรรกะ AND ซึ่งหมายความว่าสามารถรวมค่าได้ก็ต่อเมื่อตรงตามเงื่อนไขที่กำหนดทั้งหมดเท่านั้น แม้ว่าเงื่อนไขหนึ่งจะเป็นเท็จ ผลลัพธ์ก็จะกลับเป็น'0'
ไวยากรณ์และอาร์กิวเมนต์ของฟังก์ชัน SUMIFS
ไวยากรณ์ของฟังก์ชัน SUMIFS มีดังนี้:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2,...], [criterion2,...])ที่ไหน
- sum_range – ช่วงของเซลล์ที่มีค่าที่คุณต้องการรวมเมื่อตรงตามเงื่อนไขทั้งหมด
- criteria_range1 – คือ ช่วงของเซลล์ที่คุณตรวจสอบเกณฑ์1.
- เกณฑ์1 – เป็นเงื่อนไขที่คุณต้องตรวจสอบกับเกณฑ์_range1
- criteria_range2, criterion2, … – ช่วงและเกณฑ์เพิ่มเติมเพื่อประเมิน และคุณสามารถเพิ่มช่วงและเงื่อนไขอื่นๆ ให้กับสูตรได้
เราจะใช้ชุดข้อมูลในภาพหน้าจอต่อไปนี้เพื่อแสดงให้เห็นว่าฟังก์ชัน SUMIFS ทำงานอย่างไรกับเกณฑ์ต่างๆ
SUMIFS พร้อมเงื่อนไขข้อความ
คุณสามารถรวมค่าโดยอิงตามเกณฑ์ข้อความที่แตกต่างกันสองข้อในช่วงที่แตกต่างกัน ตัวอย่างเช่น สมมติว่าคุณต้องการหายอดขายรวมของสินค้าเต็นท์ที่จัดส่ง สำหรับสิ่งนี้ ให้ใช้สูตรนี้:
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"Delivered")ในสูตรนี้ เรามีเกณฑ์สองข้อ:"เต็นท์"และ"ส่งมอบแล้ว"ฟังก์ชัน SUMIFS จะตรวจสอบรายการ'Tent'(เกณฑ์1) ในช่วง A2:A13 (criteria_range1) และตรวจสอบสถานะ'Delivered'(เกณฑ์2) ในช่วง C2:C13 (criteria_range2) เมื่อตรงตามเงื่อนไขทั้งสอง มันจะรวมค่าที่สอดคล้องกันในช่วงเซลล์ D2:D13 (sum_range)
SUMIFS พร้อมเกณฑ์ตัวเลขและตัวดำเนินการเชิงตรรกะ
คุณสามารถใช้ตัวดำเนินการตามเงื่อนไขเพื่อสร้างเงื่อนไขด้วยตัวเลขสำหรับฟังก์ชัน SUMIFS
หากต้องการหายอดขายรวมของสินค้ามากกว่า 5 รายการในรัฐแคลิฟอร์เนีย (CA) ให้ใช้สูตรนี้:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")สูตรนี้มี 2 เงื่อนไข: “>5” และ “CA”
สูตรนี้จะตรวจสอบปริมาณ (จำนวน) ที่มากกว่า 5 ในช่วง D2:D13 และตรวจสอบสถานะ'CA'ในช่วง B2:B13 และเมื่อตรงตามเงื่อนไขทั้งสอง (หมายความว่าอยู่ในแถวเดียวกัน) จะรวมจำนวนเงินใน E2:E13
SUMIFS พร้อมเกณฑ์วันที่
ฟังก์ชัน SUMIFS ยังช่วยให้คุณตรวจสอบเงื่อนไขต่างๆ ในช่วงเดียวกันและช่วงต่างๆ ได้อีกด้วย
สมมติว่าคุณต้องการตรวจสอบยอดขายรวมของสินค้าที่จัดส่งหลังจากวันที่ 31/5/2021 และก่อนวันที่ 10/6/2564 จากนั้นใช้สูตรนี้:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3) สูตรข้างต้นมีสามเงื่อนไข: 31/5/2021,10/5/2021 และจัดส่งแล้ว แทนที่จะใช้ค่าวันที่และข้อความโดยตรง เราอ้างอิงไปยังเซลล์ที่มีเกณฑ์เหล่านั้น
สูตรจะตรวจสอบวันที่หลังวันที่ 31/5/2021 (G1) และวันที่ก่อน 10/6/2021 (G2) ในช่วงเดียวกัน D2:D13 และตรวจสอบสถานะ"ส่งแล้ว"ระหว่างวันที่สองวันนั้น Then, sums the related amount in the range E2:E13.
SUMIFS with Blank and Non-Blank Cells
Sometimes, you may want to find the sum of values when a corresponding cell is empty or not. To do that, you can use one of the three criteria that we discussed before: “=”, “”, and “<>”.
For example, if you only wish to sum the amount of ‘Tent’ items for which the delivery date has not been confirmed yet (empty cells), you could use criteria of “=”:
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"=")The formula looks for the ‘Tent’ item (criteria1) in column A with corresponding blanks cells (criteria2) in column C and then sums the corresponding amount in column D. The “=” represents a completely blank cell.
To find the sum amount of ‘Tent’ items for which the delivery date has been confirmed (not empty cells), use “<>” as a criteria:
=SUMIFS(D2:D13,A2:A13,"Tent",C2:C13,"<>")We just swapped “=” for “<>” in this formula. It finds the sum of Tent items with non-blank cells in column C.
SUMIFS with OR Logic
Since the SUMIFS function works on AND logic, it only sums when all conditions are met. But what if you want to sum value based on multiple criteria when any one of the criteria is met. The trick is to use multiple SUMIFS functions.
For example, if you want to add up the sales amount for either ‘Bike rack’ OR ‘Backpack’ when their status is ‘Ordered’, try this formula:
=SUMIFS(D2:D13,A2:A13,"Bike rack",C2:C13,"Ordered")
+SUMIFS(D2:D13,A2:A13,"Backpack",C2:C13,"Ordered")The first SUMIFS function checks two criteria “Bike rack” and “Ordered” and sums the amount values in column D. Then, the second SUMIFS checks two criteria “Backpack” and “Ordered” and sums the amount values in column D. And then, both sums are added together and displayed on F3. In simple words, this formula sums when either ‘Bike rack’ or ‘Backpack’ is ordered.
That’s everything you need to know to about SUMIF and SUMIFS function in Google Sheets.