SUM IF by reference

[keithabailey]

in the attached spreadsheet I need to produce the sums in I21 & I22 by reference to G21 & G22. using these numbers I need to look up all account numbers in F13:F18 and sum all matching account numbers.

e.g. Cash should = 10,000 + 1,000,000 + 0 + 100,000 + 0
AR should = 2,900,000

Any help would be appreciated

[teylyn]

based on your data sample and data sorted by account number in ascending order:

I21 = =SUMPRODUCT(--($E$13:$E$18=G21),$D$2:$D$7)

copy down to I22

[keithabailey]

Unfortunately the account numbers associated to the BS heading will not be continuous and I will not be able to sort the data based on column A.

[keithabailey]

Is anyone else able to help?

[NBVC]

Will the Account ID's in Column F be sorted ascendingly?

=SUMPRODUCT(--(LOOKUP($A$2:$A$7,$F$13:$F$18,$E$13:$E$18)=G21),$D$2:$D$7)

or if you can't sort ascendingly.... try:

=SUM(IF(ISNUMBER(MATCH($A$2:$A$7,IF($E$13:$E$18=G21,$F$13:$F$18),0)),$D$2:$D$7))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

[keithabailey]

Hi NBVC,

Unfortunately I cannot rely on column F being sorted.

I tried your

=SUM(IF(ISNUMBER(MATCH($A$2:$A$7,IF($E$13:$E$18=G21,$F$13:$F$18),0)),$D$2:$D$7))

suggestion, but this evaluated to zero.

[teylyn]

insert a new column in your data table A1 to D7 and populate it with the BS Heading value. Then summing by BS Heading is a breeze.

If you can not sort by either column A or column F, then you will need to establish some other means of getting your results. A helper column will do the trick.

Looking at your sample data, I wonder why column F is in the same order as column A. It also has the exact same amount of duplicates in the same order. Maybe your data will lend itself to a regular formula but if the relationship between E and F can not be mapped to A, you're on thin ice.

cheers

[NBVC]

After you enter my formula in, you need to hold the CTRL and SHIFT keys down and then hit ENTER. You will see { } brackets appear around the formula.

Then you can copy it down.

See attached