Day 4: Essential Formulas — Part 2

To test multiple conditions, use AND or OR inside IF. =IF(AND(A2>100, B2="North"), "Priority", "Standard") returns "Priority" only if both conditions are true. =IF(OR(A2>100, B2="North"), "Flag", "OK") returns "Flag" if either condition is true. For multiple outcome categories, nest IFs or use IFS (Excel 2019+): =IFS(A2>1000,"High",A2>500,"Medium",A2>0,"Low",TRUE,"Zero or Negative").

COUNTIF counts cells in a range matching one condition: =COUNTIF(B2:B100,"North"). For multiple conditions, use COUNTIFS: =COUNTIFS(B2:B100,"North",C2:C100,"Electronics") counts rows where Region is North AND Category is Electronics. Use wildcards for partial matches: =COUNTIF(A2:A100,"*Delhi*") counts cells containing "Delhi" anywhere.

SUMIF: =SUMIF(Region, "North", Revenue) — sums Revenue where Region equals "North". SUMIFS allows multiple conditions: =SUMIFS(Revenue, Region, "North", Category, "Electronics", Month, "Jan") — sums Revenue where all three conditions are met. Real scenario: you have 10,000 transaction rows and need total revenue by region and category. SUMIFS with a summary table referencing each combination is standard practice.

CONCATENATE (or the simpler & operator) joins text from multiple cells. =A2&" "&B2 joins first and last name with a space. =TEXT(C2,"DD-MMM-YYYY")&" - "&D2 combines a formatted date with a description. In modern Excel (2019+), TEXTJOIN is more flexible: =TEXTJOIN(", ", TRUE, A2:A10) joins a range with a delimiter and ignores blanks.

These text functions extract or examine parts of a string. LEN returns the character count. LEFT(text,n) extracts n characters from the left. RIGHT(text,n) from the right. MID(text,start,n) extracts n characters starting at position start. FIND(find_text, within_text) returns the position of a substring. Example — extract domain from email: =MID(A2, FIND("@",A2)+1, LEN(A2)-FIND("@",A2)) returns "gmail.com" from "prashant@gmail.com".