# Formula to calculate current month addition/deletion

#### ferocious12

##### Member
Dear All,

Can you help me with a formula that calculate current month addition or deletion of number of housing units based on Type (it can be sold more or some of them can be cancelled resulting in reduction in number of units sold). The problem is Types of units keep changing each month say Villa A & Villa B in April but in May it can Villa A and Villa D.

I have attached an example with desired result.

Thanks

#### Attachments

• 10.4 KB Views: 7

#### pecoflyer

##### Active Member
In D2 try =2*C2-SUMIF(\$B\$2:\$B2,B2,\$C\$2:\$C2) and pull down

#### bosco_yip

##### Excel Ninja
Or,

In D2, copied down :

=IF(COUNTIF(B\$2:B2,B2)>=COUNTIF(B:B,B2),C2-SUMIF(B\$1:B1,B2,C\$1:C1),"")

Regards

#### ferocious12

##### Member
Or,

In D2, copied down :

=IF(COUNTIF(B\$2:B2,B2)>=COUNTIF(B:B,B2),C2-SUMIF(B\$1:B1,B2,C\$1:C1),"")

Regards
this gives me blank instead of result. What may be the issue?

#### ferocious12

##### Member
In D2 try =2*C2-SUMIF(\$B\$2:\$B2,B2,\$C\$2:\$C2) and pull down
Doesn't give me the right answer. Any idea why?

#### pecoflyer

##### Active Member
Try =IF(MONTH(A2)=MONTH(TODAY()),2*C2-SUMIF(\$B\$2:\$B2,B2,\$C\$2:\$C2),"")

#### ferocious12

##### Member
Try =IF(MONTH(A2)=MONTH(TODAY()),2*C2-SUMIF(\$B\$2:\$B2,B2,\$C\$2:\$C2),"")
Not working

#### AliGW

##### Active Member
It is on your sample data:

 Month Type Units sold (Cumulative) Unit sold Division (current month) Pecoflyer Apr-20​ VillaA 50​ Apr-20​ VillaB 20​ May-20​ VillaA 70​ 20​ 20​ May-20​ VillaB 50​ 30​ 30​ May-20​ VillaD 30​ 30​ 30​ New Divisions keep adding each month Desired result

Care to share what you haven't yet told us? Under what circumstances does it fail?

#### ferocious12

##### Member
It is on your sample data:

 Month Type Units sold (Cumulative) Unit sold Division (current month) Pecoflyer Apr-20​ VillaA 50​ Apr-20​ VillaB 20​ May-20​ VillaA 70​ 20​ 20​ May-20​ VillaB 50​ 30​ 30​ May-20​ VillaD 30​ 30​ 30​ New Divisions keep adding each month Desired result

Care to share what you haven't yet told us? Under what circumstances does it fail?
Sure thanks. I have uploaded the file with Pecoflyer formula in column D (coming as blank for me) and desired result in column E

#### Attachments

• 10.8 KB Views: 8

#### pecoflyer

##### Active Member
Your requirements are illogical at least
You are looking for a result in the CURRENT month which is July. Of course the formula does not return anything since all your data end in June..
For April (rows 2 and 3), you want to return an empty cell as they contain the first sales for Villa A and B, but although it is the first sale for Villa E (row 10), the formula should return a value?
Beats me...

#### ferocious12

##### Member
Your requirements are illogical at least
You are looking for a result in the CURRENT month which is July. Of course the formula does not return anything since all your data end in June..
For April (rows 2 and 3), you want to return an empty cell as they contain the first sales for Villa A and B, but although it is the first sale for Villa E (row 10), the formula should return a value?
Beats me...
Its my bad. I want the data which subtract current month cumulative number for Villa A sales from cumulative sales number until last month. When I say current month does't mean Jul it refers to the month in column B.