Sumif or Countif - need help with correct formula

[eileen123]

Hello! I am trying to figure out which formula to use to meet these criteria: If anything (not blank) is in Column B range: B2:B100 (stays constant), I want it to add a sum of a range of cells (G2:P2) and populate it in cell R2. Then I want to drag the formula down column R to apply to the corresponding rows. I am not sure the formula I am using is correct.

I would use the same formula for certain criteria for Column A and U. Thanks for your time & help!

 

[Siddharth Rout]

I would use COUNTA() to check if there is at least one value in that range.

=IF(COUNTA(B2:B100)>0,SUM(G2:P2),"")

 

[eileen123]

This is the Error message I get in row 8, cell R8 when I tried the formula you provided. I want it to be if there is "anything" in column A or B ranges (which will be text, not numeric). The 2023 represents a year.

 

[Siddharth Rout]

This is the Error message I get in row 8, cell R8 when I tried the formula you provided. I want it to be if there is "anything" in column A or B ranges (which will be text, not numeric). The 2023 represents a year.
View attachment 72237

That is because you have an extra "(" before G8

The formula should be

=IF(COUNTA(B2:B100)>0,SUM(G8:P8),"")

and you have it as

=IF(COUNTA(B2:B100)>0,SUM((G8:P8),"")

Change the formula in cell R2 to

=IF(COUNTA($B$2:$B$100)>0,SUM(G2:P2),"")

and then simply drag it down. By adding the $ sign, the range $B$2:$B$100 becomes constant and it will not change when you drag the formula down.

 

[eileen123]

Duh! Thank you, I removed the extra "(" but this is still not giving me the result I want. In the yellow highlighted areas, I want the sum to either appear in Cell R9 OR U9 (not both) if there is anything in column A (for Cell U9) or Column B (for Cell R9). It is giving me the result of $50,000 in both R9 & U9 using the formulas you provided. I don't think it's a greater than 0 situation, it is just if there is any data in column A or B (because cell B9 is blank, there should not be $50,000 in R9). I need it to be applied to the correct program.

 

[Siddharth Rout]

So help me understand this.

If there is something in A2:A100 then populate the sum of G:P in U and keep R blank
If there is nothing in A2:A100 and if there is something in B2:B100 then populate the sum of G:P in R and keep U blank
Else both R and U remain blank

Is my understanding correct?

 

[eileen123]

Correct. And I think I am steering you wrong, and perhaps it shouldn't be a range for A & B - but just that particular cell in that row - apologies for that.

 

[Siddharth Rout]

Correct. And I think I am steering you wrong, and perhaps it shouldn't be a range for A & B - but just that particular cell in that row - apologies for that.

I am confused now. On one hand you confirm that my understanding is correct and on the other hand you mentioned "perhaps it shouldn't be a range for A & B - but just that particular cell in that row".

Can you give an example of what exactly you want??

 

[eileen123]

Apologies. This seems to be working now:

 

[Siddharth Rout]

Ah I see what you are trying to do.

Put this formula in R2 and drag it down.

=IF(A2<>"","",IF(B2<>"",SUM(G2:P2),""))

Put this formula in U2 and drag it down.

=IF(A2<>"",SUM(G2:P2),"")