# Excel {Sum(IF)} Multiple columns

Hello,

I originally had a sumifs formula that was trying to sum 49 adjacent columns. Learning this isn't possible with sumifs, I constructed the following array formula which works:

{=SUM(IF('Integrated Areas'!\$D\$7:\$D\$68=\$A4,(SUM('Integrated Areas'!\$J\$7:\$BF\$68))))}

I would like to add the following additional criteria to it, but not sure how:

=if((J5:BF5)">="&AB3, if((J5:BF5)"<"&AC3

What's the best way of doing this?

Thanks
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cost AccountantCommented:
I would try something like:

``````=SUM(N(\$D\$7:\$D\$68=\$A4)*N(\$J\$5:\$BF\$5>=AB3)*N(\$J\$5:\$BF\$5<=AC3)*\$J\$7:\$BF\$68)

=SUM(N('Integrated Areas'!\$D\$7:\$D\$68=\$A4)*N('Integrated Areas'!\$J\$5:\$BJ\$5>=AB3)*N('Integrated Areas'!\$J\$5:\$BJ\$5<=AC3)*'Integrated Areas'!\$J\$7:\$BJ\$68)
``````

This breaks the IF into an N which can be easier to understand:
``````=SUM(
N(\$D\$7:\$D\$68=\$A4)
*
N(\$J\$5:\$BF\$5>=AB3)
*
N(\$J\$5:\$BF\$5<=AC3)
*
\$J\$7:\$BF\$68
)
``````

Where there is an 'N' formula it returns 1 when true and 0 when false.
Then it multiplies each row by the N result and the values in J to BF.
This should work, though is hard to test without your workbook or a sample workbook.
Commented:
You can use this formula...

``````=Sumproduct(('Integrated Areas'!\$D\$7:\$D\$68=\$A4)*('Integrated Areas'!J5:BF5>=AB3)*('Integrated Areas'!J5:BF5<AC3)*('Integrated Areas'!\$J\$7:\$BF\$68))
``````
Saurabh...

Experts Exchange Solution brought to you by