Row Column Index Match - Sum amount when data contains a match

[STEFAWN]

Hello,

I am trying to find the sum of all charges that contain text "MGT"

In Column F3 ($0.66) and F4 ($0.15) are the correct amounts I want the formula to add.

The formula I am using is =IF(OR(B3=MGT,D3=MGT),SUM(C3,E3)C3+E3)

This is totaling both columns but I want it to return the value that contains MGT only.

Thanks A1 B1 C1 D1 E1 F1 A2 ASC 1 ASC 1 $ ASC 2 ASC 2$ Total ASC A3 MGT $0.66 405 $0.07 $0.66 A4 405 $1.34 MGT $0.15 $0.15 <tbody> </tbody><colgroup><col span="6"></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[FDibbins]

Hi, welcome to the board

I have a feeling that a big part of your problem is a very poorly laid out table. If you had 1 column for all "names" and put the data/values in the next column/s, this would be far simpler

 

[edvwvw]

A solution

=SUMPRODUCT(--(A2:A3="MGT"),(B2:B3))+SUMPRODUCT(--(C2:C3="MGT")*(D2:D3))

Agree the table could be set out to make life easier.

edvwvw

 

[FlameRetired]

Agree on the table layout. With the table laid out B1:F3 as below this array formula works.

Excel 2012
	A	B	C	D	E	F	G
1		ASC 1	ASC 1 $	ASC 2	ASC 2$	Total ASC
2		MGT	0.66	405	0.07	0.66	0.81
3		405	1.34	MGT	0.15	0.15
Sheet3

Cell Formulas
Range	Formula
G2	{=SUM(IF(($B$2:$D$3="MGT"),$C$2:$E$3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[STEFAWN]

Thank you everyone for your feedback. Unfortunately if any of you have worked with UPS/Fed Ex billing the tables are laid out like this. I utilized the formula provided by FlameRetired and it worked! Thanks again.

My only other question now would be how to do the opposite; sum the amounts that exclude MGT?

To Sum MGT I used {=SUM(IF(($B$2:$D$3="MGT"),$C$2:$E$3))}
To exclude MGT = ?

 

[FlameRetired]

Try replacing ="MGT" with <> "MGT".

 

[STEFAWN]

Thank you but that did not work - I used {=SUM(IF(($B$2:$D$3<>"MGT"),$C$2:$E$3))} and it came up with an error #N/A and should have $1.41 using the same example.

 

[FlameRetired]

Hmm.

Working on it.

What can you tell us about the headers? ASC 1 ASC 1 $ ASC 2 ASC 2$

I am investigating header patterns. Are they consistent?

 

[STEFAWN]

ASC 1 is the accessorial charge description
ASC 1$ is the accessorial charge Amount

ASC 2 is the second accessorial charge description
ASC 2 $ is the second accessorial charge Amount

The charge descriptions and amounts go on and on. Each of those are a header name. The pattern in which MGT are inconsistent though. MGT could be in either column ASC 1 or ASC 2 ...

Thanks

 

[FlameRetired]

OK if MGT etc are always under ASC # $ then this should work.

Excel 2012
	A	B	C	D	E	F	G
1		ASC 1	ASC 1 $	ASC 2	ASC 2$	Total ASC
2		MGT	0.66	405	0.07	0.66	0.81
3		405	1.34	MGT	0.15	0.15	1.41
Sheet3

Cell Formulas
Range	Formula
G2	{=SUM(IF(($B$2:$D$3="MGT"),IF(ISNUMBER(FIND("$",$C$1:$E$1)),$C$2:$E$3)))}
G3	{=SUM(IF(($B$2:$D$3<>"MGT"),IF(ISNUMBER(FIND("$",$C$1:$E$1)),$C$2:$E$3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.