Sumproduct or sumif

[JasonNeedsHelp]

Hello All,

I have in column D... variables such as "AD, IR, OA, OE, SA, SD, UR, XD".

I would like to sum everything to the right of the column (E:AA) that match "AD" and "OE" in column D. This is what i have...

=SUM(SUMIF('BG WC'!D:D,{"AD,"OE"},'BG WC'!E:AA))

but I keep getting an error...

Any help is appreciated.

Thanks!

[Richard Buttrey]

Please upload an example workbook and manually add the results you expect to see. It's not clear whether you're looking for a single cell formula for the sum of a 2 dimensional range or a formula on each row of a 2D range

[dflak]

I'm taking a stab at this but =SUMPRODUCT((E1:K5)*(D1:D5="AD"))

Assuming you have 5 rows

[Tony Valko]

With the SUMIF function all the ranges have to be the same size (99.99999999%) of the time.

You can use SUMPRODUCT.

=SUMPRODUCT(ISNUMBER(MATCH('BG WC'!D2:D100,{"AD","OE"},0))*'BG WC'!E2:AA100)

You should avoid using entire columns as references in the SUMPRODUCT function. Use smaller specific ranges.

[CAntosh]

Richard is correct, it's all guesswork without a sample, but you can use dflak's structure effectively, or the one below was my guess:

=SUM((E:AA)*(--('BG WC'!D:D="AD")))+SUM((E:AA)*(--('BG WC'!D:D="OE")))

Whatever formula you use, you'd be wise to use your actual number of rows rather than the entire column (e.g. D1:D512 instead of D:D)

EDIT: I should note, this is an array formula, use Ctrl + Shift + Enter

[dflak]

The best way to deal with variable rows of data is to convert the range to an Excel Table. Tables know how many rows they have and you can use column header names in the formulas. So when you add or delete data to a table, you do not have to change the formulas that reference the table.

[kvsrinivasamurthy]

Try this ARRAY formula. Pl see file.

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets.

[Phuocam]

Or try this ...
=SUMPRODUCT((('BG WC'!D2:D100="AD")+('BG WC'!D2:D100="OE"))*'BG WC'!E2:AA100)