# 奇数项求和公式_Excel公式求和奇数或偶数周

Here's an Excel formula challenge for you, based on an email question that someone sent to me. They wanted help with an Excel formula to sum for odd and even weeks. See my solution, and let me know how you'd solve the problem.

### 为什么总和是奇数，甚至是几周？ (Why Sum Odd or Even Weeks?)

Have you ever needed to add numbers, based on odd and even numbers in another column?

In this example, the person worked in a factory, where there are 2 work crews – Crew A and Crew B. They need to compare the production quantities for the 2 work crews.

Here's a simplified version of the production data, which we'll use for this challenge. As you can see, the crew name is not in the data.

This is the morning data from the first 4 weeks of the year.

### 识别机组人员 (Identify the Crews)

Even though the crew name is not listed, we can use the week numbers to identify which crew was working.

• Crew A works mornings on odd-numbered weeks

机组A奇数周的早晨工作

• Crew B works mornings on even-numbered weeks

机组B偶数周的早晨工作

The production data doesn't show the crew name, but we can total the odd or even week data, to get each crew's total quantity.

### 方程式挑战赛 (Formula Challenge)

Your challenge is to create a formula to calculate the total quantities for odd and even weeks

You can type the data in a blank workbook, or download my sample file with the challenge data and all the solutions that are shown below.

You can see my solution in the next section, and below that, you'll see solutions from my weekly Excel newsletter readers.

What pros and cons can you see in the other people's solutions?

If you found a different solution than the ones shown here, let me know in the comment section.

### 我的解决方案 (My Solution)

Here's my formula to sum for odd or even weeks. I used the SUMPRODUCT function, combined with ISODD and ISEVEN.

I put this formula in cell E2, to total the odd weeks:

• =SUMPRODUCT((ISODD(--($B$2:$B$11))) *($A$2:$A$11))

= SUMPRODUCT((ISODD(-($B$ 2：$B$ 11)))*($A$ 2：$A$ 11))

And here's the formula in cell E3, to total the even weeks:

• =SUMPRODUCT((ISEVEN(--($B$2:$B$11))) *($A$2:$A$11))

= SUMPRODUCT((ISEVEN(-($B$ 2：$B$ 11)))*($A$ 2：$A$ 11))

#### 这个怎么运作 (How It Works)

Here's how my formula works:

• The ISODD and ISEVEN functions return TRUE or FALSE

ISODD和ISEVEN函数返回TRUE或FALSE
• The two minus signs (double unary) convert those T/F values to numbers (-1 or 0)

两个减号(双一进制)将这些T / F值转换为数字(-1或0)
• Those results are multiplied by the Qty amounts

这些结果乘以数量
• The SUMPRODUCT function returns the total of all those multiplications.

SUMPRODUCT函数返回所有这些乘法的总和。

There are more Sumproduct examples on my Contextures website.

### 其他解决方案 (Other Solutions)

Next, here are some of the formulas that newsletter readers sent to me. You can get the data, and see all of the solutions in the Formula Challenge sample file.

#### MOD功能 (MOD Function)

Many of the solutions used the MOD function, to check if the week numbers were odd or even.

• The MOD function returns the remainder when you divide the first number (week number) by the second number (2)

当您将第一个数字(周数)除以第二个数字(2)时，MOD函数将返回余数
• If you divide an even number by 2, the remainder will be zero

如果将偶数除以2，则余数将为零
• If you divide an odd number by 2 the remainder will be 1

如果将奇数除以2，则余数将为1

For example, to find the total for odd weeks:

=SUMPRODUCT((MOD($B$2:$B$11,2)=1)*$A$2:$A$11)

= SUMPRODUCT(( MOD($B$ 2：$B$ 11,2)= 1 )* $A$ 2：$A$ 11)

### SUM或SUMPRODUCT (SUM or SUMPRODUCT)

All the solutions used SUM or SUMPRODUCT to calculate the grand total.

For example, to find the total for even weeks:

=SUM(MOD($B$2:$B$11-1,2)*$A$2:$A$11)

= SUM(MOD($B$ 2：$B$ 11-1,2)* $A$ 2：$A$ 11)

NOTE: Array Formulas

• In older versions of Excel, you'll need to array-enter the SUM  formulas, with Ctrl+Shift+Enter.

在旧版Excel中，您需要使用Ctrl + Shift + Enter 数组输入SUM公式

• If your version of Excel has Dynamic Array formulas, you won't need to do that -- just press Enter.

如果您的Excel版本具有动态数组公式，则无需这样做-只需按Enter。

#### 过滤功能 (FILTER Function)

Two of the solutions use the new FILTER function. It filters a set of numbers, based on a rule.

=SUM(FILTER($A$2:$A$11, MOD($B$2:$B$11,2)))

= SUM( FILTER ($A$ 2：$A$ 11，MOD($B$ 2：$B$ 11,2)))

In these solutions, the quantities were returned, based on using the MOD function in the week number column.

You can use these solutions if your version of Excel has Dynamic Array formulas.

### 硬编码值 (Hard-Coded Values)

In some solutions, including my original solution, there were hard-coded values. For example, the Odd week formulas used a 1, and the Even week formulas used a zero.

• =SUMPRODUCT((MOD($B$2:$B$11,2)=1) *$A$2:$A$11)

= SUMPRODUCT((MOD($B$ 2：$B$ 11,2) = 1 )* $A$ 2：$A$ 11)

• =SUMPRODUCT((MOD($B$2:$B$11,2)=0) *$A$2:$A$11)

= SUMPRODUCT((MOD($B$ 2：$B$ 11,2) = 0 )* $A$ 2：$A$ 11)

You could put those values in adjacent cells, and refer to those cells in the formula.

=SUMPRODUCT((MOD($B$2:$B$11,2)=D2)*$A$2:$A$11)

= SUMPRODUCT((MOD($B$ 2：$B$ 11,2) = D2 )* $A$ 2：$A$ 11)

Then, drag down, to use the same formula in both calculations. I like to use consistent formulas wherever possible!

Here's how I should have written my original solutions, to make it the same in both cells.

• In cell D2, type TRUE, and in D3, type FALSE

在单元格D2中，键入TRUE，在D3中，键入FALSE
• In cell E2, enter this formula, then copy it down to cell E3

在单元格E2中，输入此公式，然后将其复制到单元格E3中

=SUMPRODUCT((ISODD(--($B$2:$B$11))=D2) * ($A$2:$A$11))

= SUMPRODUCT((ISODD(-($B$ 2：$B$ 11))= D2)*($A$ 2：$A$ 11))

### 硬编码阵列 (Hard-Coded Array)

One solution used hard-coded arrays of odd and even numbers.

• =SUM(SUMIFS($A$2:$A$11,$B$2:$B$11,{1,3}))

= SUM(SUMIFS($A$ 2：$A$ 11，$B$ 2：$B$ 11 ， {1,3} ))

• =SUM(SUMIFS($A$2:$A$11,$B$2:$B$11,{2,4}))

= SUM(SUMIFS($A$ 2：$A$ 11，$B$ 2：$B$ 11， {2,4} ))

That worked alright in this small sample, with only 4 work weeks, but you'd need a flexible solution for larger samples.

### 助手列 (Helper Columns)

A few people used one or two helper columns in their solutions.  You can see those in the sample file, and I've colour coded the columns, to match the solutions in which they're used.

Helper cells or columns are useful in some situations, allowing you to break a complex formula into smaller sections that are easier to understand or troubleshoot.

But in these solutions, you'd need formulas in every data row, instead of a just 2 formulas at the top of the worksheet.

That's not too bad in a small file like this one, but could really slow things down if you're working with thousands of rows of data.

### 更多总和示例 (More Sum Examples)

There are more How to Sum examples on my Contextures website.

And remember, you can get challenge data, and see all of the solutions in the Formula Challenge sample file.

• 0
点赞
• 0
评论
• 0
收藏
• 一键三连
• 扫一扫，分享海报

04-03 723
07-18 2万+
09-08 7万+
04-24 1万+
08-09 2万+
09-01 3090
12-19 1386
11-22 1万+