Using Match Index (Excluding Blank cells)

[crazyarun2004]

Hello Experts,
I need an help to compile data based on weekdays on with respect to month. Please find attached excel for data.

Regards
Arun

[canapone]

Hi,

not sure.

In C27 to be copied down and to the right


=SUMIF($B$3:$B$9,$B27,C$3:C$9)

Regards

[davsth]

Your formulas give the same results as your desired output, so not so clear what you want!

Perhaps in c27 =SUMIFS(C$3:C$23,$B$3:$B$23,$B27)

Regards

Dav

[crazyarun2004]

Sorry, Seems i didn't attach the right file. here is the updated one.

For instance:
January's data is from C3:C9 & February is from D10:D16
whereas i wanted to compile same values from cell C39:45 and D39:45 respectively,
the formula i tried give the desired output for January,
whereas for February D3:D9 is empty and D10:D16 has data, Index and match output shows "0".

Please help me.
Regards
Arun

[Glenn Kennedy]

You can use this array formula, copied across and down:

=IFERROR(INDEX(C$3:C$23,MATCH(1,(C$3:C$23<>"")*($B$3:$B$23=$B39),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...