Iferror message with SUM and IF

[AdamSalam]

Hi everyone ..

I am working on an "Array" formula to SUM totals of specific month like so ..

=SUM(IF(MONTH($B6:$B263)=$E$6,$C6:$C263,""))

The problem is that if the user enter a value of the month like 13, for example, the total amount getting "0"

I add the IFERROR function so in that case there should be a message telling the user to enter only values between 1 and 12


I tried the below...
=IFERROR(SUM(IF(MONTH($B6:$B263)=$G$7,$C6:$C263,"")), "Month between 1-12")

unfortunately in case of error I get "0" total

can any body correct me on the same

thank you

 

[Marcelo Branco]

Try

=IF(MEDIAN(1,12,$E$6)=$E$6,SUM(IF(MONTH($B6:$B263)=$E$6,$C6:$C263,"")),"Month between 1-12")
Ctrl+Shift+Enter

M.

 

[Joyner]

How about something like this:

=IF(AND($G$7>=1,$G$7<=12),SUM(IF(MONTH($B6:$B263)=$G$7,$C6:$C263,"")), "Month between 1-12")

Do you have SUMIF or SUMIFS?

I would also use Data Validation in G7 (or wherever the user is entering the month)

 

[Joyner]

Try

=IF(MEDIAN(1,12,$E$6)=$E$6,SUM(IF(MONTH($B6:$B263)=$E$6,$C6:$C263,"")),"Month between 1-12")
Ctrl+Shift+Enter

M.

=MEDIAN(1,12,$G$7) =$G$7...Nice, thanks

 

[AdamSalam]

after extracting the Month number from the date column

<tbody>
</tbody>

I tried : =IFERROR(SUMIFS(D6:D263,C6:C263,J9),"wrong month entry") with no avail

<tbody>
</tbody>

 

[AdamSalam]

Thanks Marcelo, I will give it try , even it seems very long one
please check my last reply using sumifs

 

[AdamSalam]

Thanks Joyner,
please check my last reply using sumifs
let me know your input please

 

[AdamSalam]

Joyner
=IF(AND($G$7>=1,$G$7<=12),SUM(IF(MONTH($B6:$B263)=$G$7,$C6:$C263,"")), "Month between 1-12")
works perfect
still looking for implementing IFERROR and shorter formula, why its NOT working in this situation ?

thank you

 

[AdamSalam]

thanks Marc

=IF(MEDIAN(1,12,$E$6)=$E$6,SUM(IF(MONTH($B6:$B263)=$E$6,$C6:$C263,"")),"Month between 1-12")
Ctrl+Shift+Enter

working great

Still looking for shorter formula , and why IFERROR not working?

Appreciate you help

 

[Joyner]

still looking for implementing IFERROR and shorter formula, why its NOT working in this situation ?

IFERROR works with formula errors like #Value , #Ref , #N/A, etc, so if your formula doesn't result in one of those errors, it won't do anything.