sumifs formula one range with two criteria

I have using excel 2007.

I have two sheet one is "DATA" & Second is "SUMMARY"

Firstly if i using following formula that work perfectly.
=SUMIF(DATA!E$3:E$110,SUMMARY!E8,DATA!F$3:F$110)
=sumif(range,criteria,sum range)

In DATA sheet column e is shortcut key & column f is value.
In SUMMARY sheet e9 is a shortcut key this match with data sheet e column.
In Data sheet f2 : ae2 is month & year i.e. 52011
In Summary sheet d9 is also month & year i.e. 52011

Presently i am using following formula but below formula not work.
=SUMIFS(DATA!F3:F110,DATA!E3:E110,E9,DATA!F2:AE2=D9)

in view of the above confirm.

I am not sure if I can make sense of the logic.

You are trying to sum F3:F110 if E3:E110 matched E9, and F2:AE2 matches D9?

not sure how those connect.. are you sure about the ranges?

Hi avk,

Just checked your SUMIFs formula and found one mistake of = sign , try below:-

=SUMIFS(DATA!F3:F110,DATA!E3:E110,E9,DATA!F2:AE2,D9)

Also the last syntax i.e, DATA!F2:AE2,D9
I guess SumIFs accepts common ranges ,so try DATA!AE2:AE110,D9

But I strongly believe, you are using last syntax for row instead of column match
In case of any issue, upload a sample workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi avk,

Try using below formula:-
{=SUM(IF((DATA!$F$2:$AE$2=SUMMARY!$D8)*(DATA!$E$2:$E$110=SUMMARY!$E8),DATA!$F$2:$AE$110,""))}

Enter with Ctrl +Shift +Enter key combination

See attached:- SPARE_DATA_PRESENTATION.xlsx

regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome..

Cheers

Suggest you to mark this thread as [SOLVED]. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>