SUMIF from TABLE

[TZ Saic]

Hello,

I need help with following:

In Sheet 1 I have columns A3:M31. In column A are listed cost centers (once cost center occur more then once) and in other columns (B:M) are amounts per period (January-December). Cell F1 contains current period (e.g. 05-2015)

In sheet 2 in column L are again cost centers (only once). In column H there should be a formula that would return sum of the amounts per cost centers for period defined in cell F1 in sheet 1.

Thank you in advance.

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jayajaya_4]

pls attach a sample dear

[TZ Saic]

Hi,

Here is example if I want to sum for 04-2015. Please note that periods in cell F1 will be changes manually and therefore result in sheet 2 should change as well.

Tanja

[jayajaya_4]

=SUMPRODUCT((Sheet1!F$1=Sheet1!$B$2:$M$2)*(L2=Sheet1!$A$3:$A$31)*(Sheet1!B3:M3))


put this in h2 sheet 2...

[jayajaya_4]

attachment for ur reference..

regards
jaya

[TZ Saic]

Thank you for prompt reply. But if I change value in cell F1 to 01-2015 result in column H in sheet 2 it not OK. See result for CC 7251. It sould be for January 390, and not 650.

[jayajaya_4]

hey its working u just need to change format the date..i did it for u,,,pls see the attached

[TZ Saic]

still, when I open Excel and change the date to January, again it is not OK. I tried before to change formats, now again and I have same problem.

[TZ Saic]

I managed it with =SUMIF(Sheet1!$A$3:$A$31;$L2;INDEX(Sheet1!$B$3:$M$31;0;MATCH(Sheet1!$F$1;Sheet1!$B$2:$M$2;0))).

Thank you

[jayajaya_4]

ok fine...anyways I am glad u got a way out for this....cheers!!!!!!!!!!