Sumifs function

[Veeru106]

Hi,

i trying to use SUmifs function.the only issue is it is giving me error as Values.
that because my 2nd cretaria is horizonal not vertical means it is in columns not in Rows.
attaching a file as well.

What i need is in Col. D 21 i need sum of HR_ABC Drilling for the month of Jan.

Please look into this. Thanks

 

[bosco_yip]

Try,

D12,

=SUMIFS(INDEX($B$2:$M$14,0,MATCH(B$1,$B$1:$M$1,0)),$A$2:$A$14,$N7)

Regards
Bosco

 

[NARAYANK991]

Hi ,

Bosco has already given you a solution , but you need to take a second look at this statement :

because my 2nd criteria is horizontal not vertical means it is in columns not in Rows.

When you give the sum range as $B$2:$B$14 , it means you have already specified that you wish to sum the amounts for the month of January.

Thus specifying this to the SUMIFS function is unnecessary , and just this formula should work :

=SUMIFS($B$2:$B$14,$A$2:$A$14,$N$7)

or

=SUMIF($A$2:$A$14,$N$7,$B$2:$B$14)

Specifying the column to be summed up , by giving the month , is only required if you are indexing into the entire data range of $B$2:$N$14. This is what Bosco's formula does.

Narayan

 

[Veeru106]

Thanks Bosco and Narayan....yes that i want what you said at last...it meant for entire data range..

 

[Veeru106]

i have understood the function except , why we have given row reference to zero in Index function...request you to clarify

 

[NARAYANK991]

Hi ,

An INDEX function which has 0 as the row parameter means all rows in the range are to be considered.

Similarly , an INDEX function which has 0 as the column parameter means all columns in the range are to be considered.

Thus , a formula which is :

=INDEX($B$3:$J$37 , 0 , 7)

will refer to all the rows in the 7th column of the range , and will therefore refer to the range $H$3:$H$37.

Similarly , a formula such as :

=INDEX($B$3:$J$37 , 17 , 0)

will refer to all the columns in the 17th row of the range , and will therefore refer to the range $B$19:$J$19.

In all cases , a parameter whose value is 0 can be omitted , and the following two formulae will give the same results as the above two :

=INDEX($B$3:$J$37 , , 7)

and

=INDEX($B$3:$J$37 , 17 , )

Narayan

 

[Veeru106]

Yes that is a great explaination...Really appreciate your time and efforts..thanks once agian...