Formula to Count/Increment if condition met otherwise subtract by 1

ahsan64

New Member
Joined
Mar 29, 2022
Messages
4
Hello Everyone,

I am new to this thread and also to Excel Formulas.

I have data where I have to Increment/Count a number if the condition is met otherwise subtract the number by 1.

For example:

ColAColBColC
App_1Consumed
App_2Consumed
App_3Consumed
App_4Consumed
Base_1not_consumed
App_5Consumed
Base_3not_consumed
App_5partially_consumed
Base_4not_consumed

For ColC:

For each "Consumed" value in ColB increment the value by 1 in ColC. If there is a "not_consumed" value in ColB then subtract 1. For value"partially_consumed", do nothing or ignore.

Expected Result:

ColAColBColC
1
2
3
4
3
4
3
Do nothing
2

I was able to make the count part work but I am not sure how to subtract the value if it is "not_consumed".

Formula: =COUNTIFS(ColA$2:ColA2; "App_*"; ColB$2:ColB2;"Consumed")

My Result is:
1
2
3
4
4
5
5
5
5
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I cannot edit a post, please ignore the two App_5 values in ColA and consider them as App_5 and App_6.
Thank you in advance!
 
Upvote 0
Hi,

You didn't explain how Column A may affect the results, and based on your expected results, I don't see How Column A is involved, so try this:

Book3.xlsx
ABC
1App_1Consumed1
2App_2Consumed2
3App_3Consumed3
4App_4Consumed4
5Base_1not_consumed3
6App_5Consumed4
7Base_3not_consumed3
8App_6partially_consumed 
9Base_4not_consumed2
Sheet1072
Cell Formulas
RangeFormula
C1:C9C1=IF(B1="partially_consumed","",COUNTIF(B$1:B1,"Consumed")-COUNTIF(B$1:B1,"not_consumed"))
 
Upvote 0
Solution
Hi,

You didn't explain how Column A may affect the results, and based on your expected results, I don't see How Column A is involved, so try this:

Book3.xlsx
ABC
1App_1Consumed1
2App_2Consumed2
3App_3Consumed3
4App_4Consumed4
5Base_1not_consumed3
6App_5Consumed4
7Base_3not_consumed3
8App_6partially_consumed 
9Base_4not_consumed2
Sheet1072
Cell Formulas
RangeFormula
C1:C9C1=IF(B1="partially_consumed","",COUNTIF(B$1:B1,"Consumed")-COUNTIF(B$1:B1,"not_consumed"))
Hi Jtakw,

Thank you for the reply. Actually, I forgot to mention that the count condition is dependent on two columns i.e. ColA and ColB.

For example:
If App* and Consumed then +1 else if not_consumed then subtract. For partially_consumed, do nothing.

I made a few changes and the formula is working fine now with one issue.

=IF(B1="partially_consumed";"";COUNTIFS(B$1:C1;"consumed";A$1:A1;"App_*")-COUNTIFS(B1$2:CB1;"not_consumed";A$1:A1;"App_*"))

Result:

excel.png


The issue is for rows 5, 7 and 9, it is showing repetitive value (4,5) and not empty. I tried to use OR condition to make it empty using star Base_* condition but it not working. It is working fine for star App_* in count condition.

Not working Fine:

=IF((OR(B1="partially_consumed";A1="Base_*"));"";COUNTIFS(B1$:B1;"consumed";A$1:A1;"App_*")-COUNTIFS(B$1:B1;"not_consumed";A$1:A1;"App_*"))

Please let me know if you understand the issue.

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top