Sumif with index and match is returning too many arguments

[jollyfella]

Hi,

I am trying to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.

I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.

I am getting zero amounts.

Please see the attached file. Please help me understand where did I go wrong.

Thanks,
jf

[alansidman]

In D23, type =SUMIFS(Details!$H$4:$H$276,Details!$K$4:$K$276,'Monthly '!$A23,Details!$M$4:$M$276,'Monthly '!D$21) and copy down and across

Alan

Here is the syntax for the SUMIFS function

http://www.techonthenet.com/excel/formulas/sumifs.php

[Crisp Packet]

Hi jollyfella

Or a shorter version, in D23 copy across and down.

Please Login or Register  to view this content.

[jollyfella]

Thanks Alan. It worked well. I now understand that I was trying to paste a range so that I can match it to one value in the IFS formula.

Should this have worked with Index and Match as well? Can you also please provide me any syntax for index and match especially if I have to match multiple columns and rows.

Many Thanks for your help,

Arvind

[jollyfella]

Thanks Crisp packet. This also worked :-). Many Thanks jf

[icestationzbra]

hi arvind,

it would not have worked with the INDEX/MATCH inside SUMIF(S), if those functions were used in the "range" (or "criteria_range") argument.

the reason is that due to the construct of those functions, it is only the "criteria" argument that can handle INDEX/MATCH, whereas the "range" portion only - strictly - accepts a range of contiguous rows/cells (which would not be possible through INDEX/MATCH, unless they are being used to return a column).

that is not to mean that the "criteria" argument can only accept one value at a time - with some creativity, it can be made to accept multiple values.

i hope that explains the issue.