Excel SUMIFS: Sum by Year and Company Criteria

Question

Use SUMIFS – it lets you specify more than one criterion.
C2:C100 – the Dividend column (sum range)
A2:A100 – the Year column (first criteria range)
2025 – the year you want to sum
B2:B100 – the Company column (second criteria range)
"X" – the company you want to sum

If you prefer to use cell references for the year and company, you can write:

where D1 contains the year and E1 contains the company name.

Alternative (Office 365)
You can also use a dynamic‑array approach:

Both formulas will give you the total dividends for Company X in 2025.

Accepted Answer

<|channel|>analysis<|message|>We need to sanitize the text: remove all Chinese/Japanese/Korean characters and words from Russian or English text. The text is English. We need to remove all CJK characters. The text is in English, but there might be some Chinese characters? Let's scan. The text includes some Chinese words? I see "及时发现, 容器, 正常工作, 可以, 使用, 如何, 什么, 问题, 解决, 的, 是, 不, 了, etc." But those are just examples in the instruction. The actual content is English. But there might be some Chinese characters inadvertently? Let's check the content: The content is all English. I don't see any Chinese characters. But we need to check for any Japanese or Korean characters. The text includes "Office 365" etc. No CJK. So we might not need to remove anything. But we need to check for any hidden characters. The text includes "C2:C100" etc. No CJK. So the sanitized text is identical to the original. But we must preserve Markdown formatting exactly. So we output the same text. But we need to ensure no foreign characters. The text includes "C2:C100" etc. Those are Latin. "Office 365" is Latin. "SUMIFS" is Latin. "Excel" is Latin. "C2:C100" is Latin. "A2:A100" etc. "2025" numeric. "X" Latin. "Y" Latin. "C:C" etc. "D1" etc. "E1" etc. "SUM(FILTER(C:C, (A:A=2025)*(B:B="X")))" etc. All Latin. So no removal needed. But we need to check for any hidden characters like "–" (en dash) or "—" (em dash). Those are punctuation, allowed. The instruction says keep punctuation. So it's fine. We need to preserve Markdown formatting exactly. So we output the same text. But we need to check for any stray Chinese characters that might be hidden. Let's scan the text carefully: The first paragraph: "To sum dividends or any values in Excel Office 365 using multiple criteria like year and company, the SUMIFS function is your go-to tool—it handles two or more conditions with AND logic effortlessly. Try =SUMIFS(C2:C100, A2:A100, 2025, B2:B100, "X") where C is dividends, A is years, and B is companies; this pulls the exact total for Company X in 2025 (say, 8.91 from your data). For flexibility, swap hardcoded values with cell refs like =SUMIFS(C:C, A:A, D1, B:B, E1), or go dynamic in Office 365 with =SUM(FILTER(C:C, (A:A=2025)*(B:B="X"))). Both nail multiple criteria sums without breaking a sweat." No CJK. The "## Contents " etc. All English. The "## What is the SUMIFS Function in Excel? " etc. All English. The "## SUMIFS Syntax for Multiple Criteria " etc. All English. The "## Step-by-Step SUMIFS Example: Dividends by Year and Company " etc. All English. The "## SUMIF vs SUMIFS: When to Switch for Multiple Criteria " etc. All English. The "## Troubleshooting Common SUMIFS Errors " etc. All English. The "## Advanced SUMIFS Tips for Office 365 " etc. All English. The "## Sources " etc. All English. The "## Conclusion " etc. All English. Thus no removal needed. But we need to check for any hidden characters like """ or """ or "'" or "'" or "—" etc. Those are punctuation, allowed. The instruction says keep punctuation. So it's fine. Thus the sanitized text is identical to the original. We just output the same text. But we need to ensure no stray characters like """ or """ that might be considered foreign? They are punctuation, not foreign. The instruction says keep punctuation. So it's fine. Thus final output: the same text. But we need to double-check that there are no Chinese characters in the text. Let's search for any characters outside the ASCII range. The text includes "—" (em dash). That is punctuation. It's fine. The text includes """ and """ maybe? Actually the text uses """ and """? Let's check: The text uses """ and """? I see """ maybe in the code block? Actually the text uses """ in the code block? Let's check: The code block uses backticks and quotes. The quotes inside code block are standard ASCII double quotes. The text uses """ maybe in the phrase "—" or "—"? The text uses "—" (em dash). That is punctuation. It's fine. Thus we output the same text. But we need to preserve Markdown formatting exactly. So we output the same text. Thus final answer: the same text. But we need to ensure no trailing spaces or modifications. So we output exactly the same content. Let's copy the entire content exactly as given. Ensure no modifications. Let's check the original content again: =SUMIFS(sumrange, criteriarange1, criteria1, [criteria_range2, criteria2], ...) =SUMIFS(C2:C100, A2:A100, 2025, B2:B100, "X") =SUMIFS(C:C, A:A, D1, B:B, E1) =SUM(SUMIFS(C:C, A:A, 2025, B:B, "X"), SUMIFS(C:C, A:A, 2025, B:B, "Y")) We need to output exactly that. Ensure no modifications. So final answer: same text.<|end|><|start|>assistant<|channel|>final<|message|>To sum dividends or any values in Excel Office 365 using multiple criteria like year and company, the SUMIFS function is your go-to tool—it handles two or more conditions with AND logic effortlessly. Try =SUMIFS(C2:C100, A2:A100, 2025, B2:B100, "X") where C is dividends, A is years, and B is companies; this pulls the exact total for Company X in 2025 (say, 8.91 from your data). For flexibility, swap hardcoded values with cell refs like =SUMIFS(C:C, A:A, D1, B:B, E1), or go dynamic in Office 365 with =SUM(FILTER(C:C, (A:A=2025)*(B:B="X"))). Both nail multiple criteria sums without breaking a sweat. Contents What is the SUMIFS Function in Excel? SUMIFS Syntax for Multiple Criteria Step-by-Step SUMIFS Example: Dividends by Year and Company SUMIF vs SUMIFS: When to Switch for Multiple Criteria Troubleshooting Common SUMIFS Errors Advanced SUMIFS Tips for Office 365 Sources Conclusion What is the SUMIFS Function in Excel? Ever tried adding up sales, dividends, or expenses but only for specific years and teams? That's where SUMIFS Excel shines. Unlike basic SUM, it sums a range only if rows match all your criteria—like 2025 and Company X. Introduced in Excel 2007 and perfected in Office 365, SUMIFS supports up to 127 criteria pairs. It's case-insensitive, ignores blanks by default, and works lightning-fast on big datasets. According to the official Microsoft documentation, it's ideal for sum with multiple criteria Excel scenarios, pulling totals like regional sales or yearly dividends without manual filtering. Why bother? Filters and pivot tables work, but formulas stay dynamic. Change a year in D1? Total updates instantly. Simple as that. SUMIFS Syntax for Multiple Criteria Getting the syntax right unlocks everything. The basic structure? sum_range: What to add up (e.g., dividends in C2:C100). criteria_range1: First condition column (e.g., years in A2:A100). criteria1: Match value (e.g., 2025 or ">2024"). Repeat for more criteria—always paired. Ranges must match in size, or you'll hit errors. For your setup: Here, it sums C only where A=2025 and B="X". Use quotes for text/numbers with operators, like ">=2025" or "*Corp". Microsoft's guide on multiple conditions shows this yielding spot-on results, like 14,719 in their sales example. Pro tip: Formula AutoComplete in Office 365 suggests pairs as you type. No more guesswork. Step-by-Step SUMIFS Example: Dividends by Year and Company Let's use real data. Imagine this table (rows 2-6): | Year (A) | Company (B) | Dividend (C) | |----------|-------------|--------------| | 2025 | X | 3.20 | | 2025 | Y | 2.50 | | 2025 | X | 5.71 | | 2024 | X | 1.00 | | 2025 | X | 0.00 | Want Company X's 2025 total? (Should be 8.91.) Click a blank cell (say, F2). Type: =SUMIFS(C2:C6, A2:A6, 2025, B2:B6, "X") Hit Enter. Boom—8.91. For dynamic use, put 2025 in D1, "X" in E1: Drag it down for other companies. Exceljet's breakdown confirms this exact formula works across sheets, even with wildcards like "X*". And Ablebits tutorial maps it perfectly to multi-column sums. What if data spans 100 rows? Use C:C for the whole column—Office 365 handles millions effortlessly. SUMIF vs SUMIFS: When to Switch for Multiple Criteria SUMIF? Great for one condition, like =SUMIF(A:A, 2025, C:C) (all 2025 dividends). But add company? It chokes—only single criteria. Microsoft explicitly says: for multiple criteria sum Excel, jump to SUMIFS. SUMIF's syntax =SUMIF(range, criteria, [sum_range]) can't AND conditions. Need OR? That's advanced territory later. Stuck on older Excel? SUMIFS is backward-compatible since 2007. Why fight SUMIF limitations when SUMIFS just works? Troubleshooting Common SUMIFS Errors Formula returns 0? Or #VALUE!? Here's the fix. 0 result: Check data types—years as text? Convert with VALUE(). Criteria mismatch? Use wildcards "X". #VALUE!: Ranges differ in size (C2:C100 vs A2:A99). Fix: Match exactly. Wrong decimals: Regional settings? Use commas or semicolons (; in some locales). Blanks summing: SUMIFS skips them, but wrap in IF for zero-handling. ExtendOffice tips nail operators like ">150". Test small ranges first. Still stuck? Evaluate formula (Alt+T, U, F) steps through it. Advanced SUMIFS Tips for Office 365 Office 365 unlocks more. OR logic? No single SUMIFS does it, but: Or array magic: =SUM(FILTER(C:C, ((A:A=2025)*(B:B="X"))+(A:A=2024))). Exceljet's OR guide and DataCamp overview show arrays crushing complex sums. Wildcards? "X?" for XY. Dates? DATE(2025,1,1). Combine with INDEX/MATCH for lookups. Suddenly, dashboards build themselves. Sources SUMIFS function — Official Microsoft syntax and basic examples for multiple criteria: https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b Sum values based on multiple conditions — Microsoft step-by-step for SUMIFS with paired criteria ranges: https://support.microsoft.com/en-us/office/sum-values-based-on-multiple-conditions-e610ae0f-4d27-480c-9119-eb644f1e847e SUMIFS Function — Exceljet detailed syntax, examples like year/company sums, and error fixes: https://exceljet.net/functions/sumifs-function Excel SUMIFS with multiple criteria — Ablebits practical formulas for column-based criteria matching: https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/ SUMIF function — Microsoft comparison showing SUMIF limits vs SUMIFS for multi-criteria: https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b SUMIFS with multiple criteria and OR logic — Exceljet advanced OR techniques using SUM and arrays: https://exceljet.net/formulas/sumifs-with-multiple-criteria-and-or-logic Excel SUMIF multiple criteria — ExtendOffice operators and troubleshooting for SUMIFS setups: https://www.extendoffice.com/documents/excel/2471-excel-sumif-multiple-criteria.html Excel SUMIFS Excel Tutorial — DataCamp guide to wildcards, operators, and Office 365 features: https://www.datacamp.com/tutorial/sumifs Conclusion Master SUMIFS Excel for any sum multiple criteria task—whether dividends by year and company or sales by region and quarter—and you'll save hours on reports. Start with the simple =SUMIFS(C:C, A:A, yearcell, B:B, companycell) syntax, tweak for errors, and level up to arrays in Office 365. It's reliable, dynamic, and beats manual work every time. Give it a spin on your data today.