SUMIF but not if cell is blank

[Gtrtim112]

I'm not sure why SUMIFs confuse me so much but they do... I'm hoping someone can work this out for me

I have the following formula in place and it works great. However, I need to add another IF function to it and don't know how. If A20 is blank, I don't want anything added at all.

Please Login or Register  to view this content.

[jeffreybrown]

Try...

=IF(Kronos1!A20="","",SUMIF(Kronos1!$C$11:$C$1009,"*"&Kronos1!D11&"*",Kronos1!$F$11:$F$1009))

[Sam Capricci]

I'm thinking that simply adding an if statement to the beginning might do it.
something like ... IF(Kronos1!A20="","",SUMIF(Kronos1!C11:C1009,"*"&Kronos!$D11&"*",Kronos1!F11:F1009))
guessing that the A20 is in the Kronos1 sheet.

EDIT: I see i'm not the first to come up with that idea.

[MrChrisP]

jeffreybrown and Sambo kid - Sorry to piggyback on someone elses post, but would the above suggestions from you both work in theory if I needed the formula to do the same but when a single cell within an entire column was blank?

For example A1 = 1, A2 = 2, A3 = 3, A4 = BLANK, A5 = 5

I've tried it as below:
IF(Kronos1!A:A="","",SUMIF(Kronos1!C11:C1009,"*"&Kronos!$D11&"*",Kronos1!F11:F1009))

but it doesn' t seem to work.

[jeffreybrown]

Hi MrChrisP,

My first thought, no. As written that probably will not work as you desire.

We do have a rule about hijacking a thread. If you have a pertinent question of your own, please start your own thread, and if necessary, link back to this thread if you believe it will help.

Also, often times it is easier to answer a query when a sample workbook is attached with a clear before and after.

[Phuocam]

Delete post.

[MrChrisP]

Hi jeffreybrown,

Thanks for the response and apologies I must have missed the rule.

I don't have a specific reason for my question it was more just to gain a better understanding of how the formula works. I saw the question and it perked my interest is all.

I'll have another read through the rules and will make sure I stick by them from now on.

Many thanks,
MrChrisP

[Gtrtim112]

Try...

=IF(Kronos1!A20="","",SUMIF(Kronos1!$C$11:$C$1009,"*"&Kronos1!D11&"*",Kronos1!$F$11:$F$1009))

A20 is located on the active sheet, so having Kronos1!A20 wouldn't be correct but I adjusted the formula to:

Please Login or Register  to view this content.

The code you provided will add the figures as needed but if A20 is blank, the formula you provided still adds the figures. Since A20 actually has a formula in it, do you believe that is what might be causing the issue? The formula in A20 is a simple ='Sheet1'!A3 formula.

[jeffreybrown]

How about attaching a sample workbook!

[Pete_UK]

Are you sure that your SUMIF function actually returns a value other than 0? You might have your sheet set up to show blanks instead of zeros.

This is the correct syntax for your final attempt:

=IF(A20<>"",SUMIF(Kronos1!$C$11:$C$1009,"*"&Kronos1!D11&"*",Kronos1!$F$11:$F$1009),"")

Hope this helps.

Pete

[Gtrtim112]

Are you sure that your SUMIF function actually returns a value other than 0? You might have your sheet set up to show blanks instead of zeros.

Sorry Pete... no dice. It still didn't work. I will attach an example in response to Jeffrey.

[Gtrtim112]

How about attaching a sample workbook!

Sorry I didn't sooner. The actual workbook is pretty huge. I had to remove several macros, sensitive info, etc. Typically, when I ask for assistance, the cell numbers I reference may not be exact. I just have to get the premise of the formula and adjust cell numbers to my actual workbook.

With that being said, I am attaching an abbreviated example with no macros. You'll be looking at column V. You will see the formula above in the cells of that column, with the adjustments made for the actual workbook.

Let me know if there are any questions but I think I laid it out in the file so that you will see what I'm after.

[canapone]

Hi All,

a possible approach:


=IF(T2>"A",SUMIF(Kronos1!C$2:C$1000,"*"&D2&"*",Kronos1!F$2:F$1000),"")

Out of curiosity: you could shorten the formula

=IF(T2>0,SUMIF(Kronos1!C$2:C$1000,"*"&D2&"*",Kronos1!F$2),"")



Hope that helps

[Gtrtim112]

Hi All,
a possible approach:
=IF(T2>"A",SUMIF(Kronos1!C$2:C$1000,"*"&D2&"*",Kronos1!F$2:F$1000),"").... Hope that helps

By golly, that did it!

This forum never ceases to amaze me. Everyone is so helpful. I certainly wish I could return the favor.

Thanks so much for everyone's assistance!