• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum of Expense Types with multiple criteria in columns and rows

A De Luna

New Member
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
 

Attachments

  • Book1.xlsx
    82.4 KB · Views: 2
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
 
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
 
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
 
Back
Top