• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif Help

C

Chetan

New Member
Hello There, i am stucked at one step.
i have a month wise data and wanted to sum only till the month selected by User.

ex. if a user wants a data only till Feb, then the sum should be only for 02 months, i.e. Jan and Feb.

Have attached a Sample excel file for reference.

Thanks in Advance.
Chetan
 

Attachments

  • Sumif Help.xlsx
    14.4 KB · Views: 7
Hello Sir,

if i put "Not Applicable" instead a value in the data, your formula throws an Error message "#VALUE!".

Is there any way to ratify this Error message .?

Thanks for your Help.

Chetan
 

Attachments

  • Sumif Help.xlsx
    14.4 KB · Views: 6
Would using 0 instead of Not Applicable assist?
Then apply a custom Number format to the Range: D3:O17 of:
0,00#;-0,00#;"Not applicable"

That way 0 will not impact the calculations, and where it is 0 it will display "Not applicable"
- - - - - -
If you don't like that try:
D24:
=SUMPRODUCT(IFERROR(1*$D$3:$O$17,0)*($C$3:$C$17=D21)*($D$2:$O$2>=D2)*($D$2:$O$2<=D22)) Ctrl+Shift+Enter
 
Hi Chetan,

Idea for using 0 as "Not applicable" is great.

Here a few more:

Text will be ignored if you replace the SUMPRODUCT with SUM(IF {array formula}:
=SUM(IF(($C$3:$C$17=D21)*($D$2:$O$2>=D2)*($D$2:$O$2<=D22),$D$3:$O$17))
Enter with CSE

Edit:
I forgot, This should also work if you replace * with ,

=SUMPRODUCT($D$3:$O$17,($C$3:$C$17=D21)*($D$2:$O$2>=D2)*($D$2:$O$2<=D22))

And this little long version but doest not requires CSE:
=SUM(INDEX(C3:C17,MATCH(D21,C3:C17,0)):INDEX(D3:O17,MATCH(D21,C3:C17,0),MATCH(D22,D2:O2,0)))

Regards,
 
You must log in or register to reply here.
Back
Top