Help required for an appropriate formula

[Kumar@raja]

Hi Friends,

Kindly provide an appropriate formula for the attached File. Here wanted the sum of amount against each Head of account which is capturing the data from C for respective month in all columns from F to Q.

Have tired with Sumifs function but did not fetch the result. Kindly help me out.

Your support for the same is appreciated.


Best,
Kumar

 

[Naresh Yadav]

Dear Kuma,

pls try this one

=SUMPRODUCT((--TEXT($A$2:$A$91,"MMM-YY")=F$5)*($B$2:$B$91=$E6)*($C$2:$C$91))

 

[shrivallabha]

SUMIFS probably doesn't like construct like MONTH() in it so it doesn't work but you can use SUMPRODUCT.
=SUMPRODUCT($C$2:$C$91,--($B$2:$B$91=$E6),--(MONTH($A$2:$A$91)=MONTH(F$5)))

Edit: Didn't see ghat the YEAR was changing which Naresh's formula handles nicely.
=SUMPRODUCT($C$2:$C$91,--($B$2:$B$91=$E12),--(MONTH($A$2:$A$91)=MONTH(Q$5)),--(YEAR($A$2:$A$91)=YEAR(Q$5)))

You won't get incorrect results with first formula as the MONTHS seem to be relating to Fiscal year but it is better to be cautious.

 

[Kumar@raja]

Dear Naresh & Shrivallabha,

Thank you very much for the support Thanks a lot .

Sincerely would like to check one thing here, how one can write these kind of formulas, as the same always a big difficult task for me. Is there any online/offline training thru our forum on these logical functions to write an easy manner . Kindly let me know, if yes.

Once again thanks for taking your time for supporting in this regard.

Best,
Kumar

 

[shrivallabha]

It is good to know that you want to learn the skill rather than leaning for help.

There are several courses online. And Chandoo offers some of these.
http://chandoo.org/wp/excel-school/

If you like to read then there are several books written on all aspect. There's lot of online reading material as well e.g. Vault section, Formula Forensics, Formula Challenges on this website.

For practicing there are several real world issues available on forums.

So choose the best method that suits you and start learning. Happy learning

 

[Somendra Misra]

Hi,

Just another formula in F6 and copy down & across:

=SUMIFS($C$2:$C$91,$B$2:$B$91,$E6,$A$2:$A$91,">="&F$5,$A$2:$A$91,"<="&EOMONTH(F$5,0))

Regards,