Sumifs function

[pzulfugarli]

HI,

I am trying to form coskewness matrix for the portfolio. I need to sum up values using 3 criteria. I typed the following formula but the result is "value error"

=SUMIFS(I3:N23,H3:H23,R3,G3:G23,S3,I2:N2,T3)

Can't find where is the mistake in the formula...

All usefull comments are appreciated.

Thank you in adavance,
Pasha

 

[Kickingkangaroos]

Hi pzulfugarli,

To confirm, the syntax for SUMIFS is as follows:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2...)

For this rule to work, each range must only be within a single column - you appear to have two separate ranges which span over multiple columns: "I3:N23" and "I2:N2".

A workaround for this would be adding together mutiple SUMIFS, to get your desired total sum.

 

[Jonmo1]

Welcome to the board.

Try
=SUMPRODUCT((G3:G23&" "&H3:H23=S3&" "&R3)*(I2:N2=T3)*(I3:N23))


Hope that helps.

 

[BeingYogi]

May Be:


{=SUM(IF(H3:H23=R3,IF(G3:G23=S3,IF(I2:N2=T3,I3:N23))))}

Array Formula

 

[pzulfugarli]

Thank you for your quick replies. I do really appreciate.

Once more, I want to make more clear what I need to do.

<TABLE style="WIDTH: 255pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=340><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" id=td_post_2819306 class=xl70 height=21 width=84> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4bd97; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl73 width=64>Criteria 3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c4bd97; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 width=64 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c4bd97; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 width=64 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c4bd97; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl75 width=64 align=right>7</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl71 height=21>Criteria 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl76>Criteria 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl71 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77 align=right>2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #538dd5; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl79 rowSpan=3 colSpan=3>Sum range</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl71 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77 align=right>3</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl72 height=21 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c4d79b; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl78 align=right>1</TD></TR></TBODY></TABLE>

I need to sum the numbers from this table using 3 cirterias shown in the table. I guess the problem with sumif is "sumrange" and "Criteria 3"
Does anybody any other function that can be used to perform this task??

Pasha

 

[pzulfugarli]

Jonmo 1,

Thank you very much.. the formula
"=SUMPRODUCT((G3:G23&" "&H3:H23=S3&" "&R3)*(I2:N2=T3)*(I3:N23))" you provided works.

Once more appreciated,
Pasha

 

[Jonmo1]

Glad to help, thanks for the feedback.