Need your help sum cells with specific criteria

[Valdas]

I need to sum only those cells that have the letter "p" in the right column.
This is demo, my row is very long, last column QZ.

Thanks You!!!

 

[Aniruddah Alam]

I need to sum only those cells that have the letter "p" in the right column.
This is demo, my row is very long, last column QZ.

Thanks You!!!View attachment 1028

Hello Valdas,

Thank you for reaching out to our forum for assistance. Unfortunately, we weren't able to sum up all the cells in a row based on the criteria you provided. However, I have created a custom function called "Sum_with_Condition" using VBA code to help you achieve your desired results. To guide you better, I have attached a sample file in .xlsm format that illustrates how to use this function. Additionally, I have also provided a screenshot to help you better understand how to use this function.


Sum_with_Condition(first_cell, last_cell, criteria)
Here,
first_cell: This is the starting cell in the row from which you want to begin summing the values.
last_cell: This is the ending cell in the row where you want to stop summing the values.
criteria: This is a string that represents the condition you want to check in the cells to the right of each value.
Note: Unlike the default functions in Excel, this function will not calculate the value automatically whenever the worksheet gets changed by the user. So you have to manually trigger the recalculation of this custom function by going to the cells where you have used this function and re-entering the formula.

Please let me know if you have any further questions or issues.

Regards
Aniruddah
Team Exceldemy

PS: Please make sure to enable macro before using the function.

 

[Valdas]

Hello Valdas,

Thank you for reaching out to our forum for assistance. Unfortunately, we weren't able to sum up all the cells in a row based on the criteria you provided. However, I have created a custom function called "Sum_with_Condition" using VBA code to help you achieve your desired results. To guide you better, I have attached a sample file in .xlsm format that illustrates how to use this function. Additionally, I have also provided a screenshot to help you better understand how to use this function.
View attachment 1029

Sum_with_Condition(first_cell, last_cell, criteria)
Here,
first_cell: This is the starting cell in the row from which you want to begin summing the values.
last_cell: This is the ending cell in the row where you want to stop summing the values.
criteria: This is a string that represents the condition you want to check in the cells to the right of each value.
Note: Unlike the default functions in Excel, this function will not calculate the value automatically whenever the worksheet gets changed by the user. So you have to manually trigger the recalculation of this custom function by going to the cells where you have used this function and re-entering the formula.

Please let me know if you have any further questions or issues.

Regards
Aniruddah
Team Exceldemy

PS: Please make sure to enable macro before using the function.

Thank You very mach

 

[Aniruddah Alam]

Thank You very mach

You are most welcome. It was our pleasure to assist you! Feel free to reach out again if you need any more help. Have a wonderful day!

 

[Valdas]

Thank You very mach

I have one more question, maybe You can help me again?

 

[Aniruddah Alam]

I have one more question, maybe You can help me again?

View attachment 1031

Sure, to achieve your goal, you can use the following approach:

• First, create a helper column in the first sheet (Lapas1 in this case) to calculate the total "p" in each row using the COUNTIF function.

=COUNTIF(B2:F2,"p")​

• Next, go to the 2nd sheet and use the SUMIF function to sum the total "p" for each person (Jhon, Smit, etc).
  

=SUMIF(Lapas1!$A$2:$A$6,Lapas2!C4,Lapas1!$G$2:$G$6)​

Here,​

• Lapas1!$A$2:$A$6 is the criteria range (column containing Names from 1st sheet)
• Lapas2!C4 is the criteria
• Lapas1!$G$2:$G$6 is the sum range (column containing Total p in row from 1st sheet )

Make sure to use the absolute reference properly so that you can autofill the formula for the cells below.
I have attached the updated file below. Please let me know if you need any further assistance.

Regards
Aniruddah
Team Exceldemy