SUMIF + OFFSET + IFERROR
JLM2856
Posted messages
476
Registration date
Status
Member
Last intervention
-
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Hello,
I am using a SUMIF formula with 3 basic criteria and a 4th condition that allows me to retrieve the data I want from the correct column based on the corresponding designation (MATCH + OFFSET).
It seems to work, yet I don't understand why it always gives me the data corresponding to the next line rather than the one I want.
Perhaps there is an error in my formula:
{=SUM(IF((sheet1!$A:$A=sheet3!B$1)*(sheet1!$B:$B=sheet3!B$2)*(sheet1!$C:$C=sheet2!$B$2),OFFSET(sheet1!$F$2,0,MATCH($A$1,sheet1!$F$1:sheet1!$I$1)-1,5000,1)))}
If anyone can find out why this is happening, it would help me tremendously.
Thank you.
I am using a SUMIF formula with 3 basic criteria and a 4th condition that allows me to retrieve the data I want from the correct column based on the corresponding designation (MATCH + OFFSET).
It seems to work, yet I don't understand why it always gives me the data corresponding to the next line rather than the one I want.
Perhaps there is an error in my formula:
{=SUM(IF((sheet1!$A:$A=sheet3!B$1)*(sheet1!$B:$B=sheet3!B$2)*(sheet1!$C:$C=sheet2!$B$2),OFFSET(sheet1!$F$2,0,MATCH($A$1,sheet1!$F$1:sheet1!$I$1)-1,5000,1)))}
If anyone can find out why this is happening, it would help me tremendously.
Thank you.
1 answer
Hello
hard to interpret without the file and the database configuration and the question is not very clear..
..you say you have the result of the following line, while your OFFSET field assumes a total of 5000 rows??
If, in reality, it is indeed a column offset as I think, check somewhere in a cell the value that the formula alone gives:
=MATCH(A1:tab1!F1:I1,0)-1 and see if it is indeed the offset desired compared to F2, (or could it not be rather compared to F1)(since MATCH starts in F1)
otherwise, come back with more info
sincerely
Ps once this point is settled, you have lighter formula options than the array one for this calculation with Excel from 2007:
=SUMIFS(OFFSET(tab1!$F$2,0,MATCH($A$1,tab1!$F$1:$I$1)-1,5000,1),tab1!$A:$A,B1,tab1!B$B,tab3!B$2,tab1!$C:$C,tab2!$B$2)
To err is human, to persist is diabolical.
hard to interpret without the file and the database configuration and the question is not very clear..
..you say you have the result of the following line, while your OFFSET field assumes a total of 5000 rows??
If, in reality, it is indeed a column offset as I think, check somewhere in a cell the value that the formula alone gives:
=MATCH(A1:tab1!F1:I1,0)-1 and see if it is indeed the offset desired compared to F2, (or could it not be rather compared to F1)(since MATCH starts in F1)
otherwise, come back with more info
sincerely
Ps once this point is settled, you have lighter formula options than the array one for this calculation with Excel from 2007:
=SUMIFS(OFFSET(tab1!$F$2,0,MATCH($A$1,tab1!$F$1:$I$1)-1,5000,1),tab1!$A:$A,B1,tab1!B$B,tab3!B$2,tab1!$C:$C,tab2!$B$2)
To err is human, to persist is diabolical.