Sum of Expense Types with multiple criteria in columns and rows

[A De Luna]

In the attached file, I'm trying to add revenue, COGS, SG&A, and other segment codes by "job types":

Job Types:
Other
Construction
Containments
Environmental/Cleanup
Fencing
Generators
Heaters
Heavy Equipment
Impoundments
Light Tower
Misc
Miscellaneous
New Contents
Pad Sites
Poly
Rental
Rental Other
Re-Rents
Rig Moving
Roads
Roustabout
Water Transfer 10"

I looked at several very similar examples but not quite like this one. Your help is greatly appreciated. Thank you.

Regards,

ADL

 

[Deepak]

Pls explain bit more!!

 

[NARAYANK991]

Hi ,

Something like this ?

=SUMPRODUCT(($B$2:$B$93=$B97)*($C$1:$FI$1=$A97)*Data)

where Data is a named range , referring to :

='Rents -- 2012'!$C$2:$FI$93

Narayan

 

[Nunes]

Hi,

I solved it with the same method NARAYANK991 did, but with a little twist, cuz his methos will return N/A because some of your data are N/A's that will bust the sumproduct formula if not handled

I just introduced na IFERROR(,0) in each parameter of the sumproduct and evaluated the formula with Ctrl-Shift-Enter

the resulting formula is
=SUMPRODUCT(IFERROR(($B$2:$B$93=$B97);0)*IFERROR(($C$1:$FI$1=$A97);0)*IFERROR($C$2:$FI$93;0))
this formula should be in cell c97

Don't Forget to evaluate it with Ctrl-Shift-Enter
You then copy paste it down the array you need

Best
Nuno

 

[NARAYANK991]

Hi ,

I forgot to mention this ; the problem is not the errors in the data ; the problem is the #N/A text in the headers $C$1:$FI$1.

I replaced all instances of #N/A in the headers by the more appropriate N.A.

Narayan

 

[A De Luna]

Gentlemen,

Thank you. It worked.

Regards,

ADL

 

[A De Luna]

Pls explain bit more!!

Deepak,

Thank you for your inquiry. I received the answer I needed.

ADL