Sum all rows in a column if column header meets certain text criteria

Question

Say I have 5 column headers that repeat 3 times. Apples, Oranges, Bananas, Grapes, Plums, Apples, Oranges, Bananas...etc. The headers are all in row 2. Underneath (Row 3 - Row 230) each header are counts.

I want to sum all of the counts underneath each column where the header is "Apples".

I'm using this formula: =SUMIF(I2:AW2,"Apples",I3:AW230)

The SUM I get back is off by a large factor. Anyone know what I'm doing wrong?

Scott Craner · Accepted Answer · 2020-04-22 22:08:51Z

1

use SUMPRODUCT():

=SUMPRODUCT((I2:AW2="Apples")*I3:AW230)

One note to this, if there are any text or errors in I3:AW230 this will error.

if there is text or error we need to resort to an array formula of SUM:

=SUM(IFERROR((I2:AW2="Apples")*I3:AW230,0))

Being an array formula and depending on one's version of Excel this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If one has the Dynamic Formula FILTER then they can use:

=SUM(FILTER(I3:AW230,I2:AW2="Apples"))

edited Apr 22, 2020 at 22:08

answered Apr 22, 2020 at 21:47

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

istillhavetogopee Over a year ago

There is text. Is there a way to account for this?

Collectives™ on Stack Overflow

Sum all rows in a column if column header meets certain text criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related