Use countif based on value and cell color in formula

Zazu_0420

New Member
Joined
Aug 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hey

I am currently creating an overview Excel for our communication project.
In rows 7, 8, 9 and 10 (starting from column C) all the planned social media posts will be filled in. differentiating the type of post. 1 being a static visual, 2 dynamic template, 3 a video and 4 a premade template.
Each type has a specific cost. For easy counting lets use the following values:

1: 100
2: 200
3: 300
4: 50

I wanted one cell which contained the total cost of all the post. Therefore I used the following formula:

=(countif(C7:XFD9;1)*100)+countif(C7:XFD9;2)*200)+countif(C7:XFD9;3)*300)+countif(C7:XFD9;4)*50)


For previous project there wasn't an issue because all posts were posted on the same account.
For this new project we have several diffrent accounts and I would like to assign a specific color to each one.

Main account: green (A8)
Second account: Pink (A9)
tertiary account: Yellow (A10)

As before I would like to calculate the total cost of all the different posts but only in cell A8 only for the main account (green), cel A9 for the second account (pink) and in A9 only for our tirtiary account.
So the formula will need to take the color of the cells into account.

Creating an UDF so I can use it in other formulas would be ideal.

I tried to modify the CountcColor VBA code to add an additional condition but can't seemd to make it work and don't really see how I could put it in the above formula.
Been looking online for several hours and can't seem to find it. :/


Thanks in advance!

Master file Elite Series KOPIE.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBK
1ZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDoVrZaZoMaDiWoDo
21-nov2-nov3-nov4-nov5-nov6-nov7-nov8-nov9-nov10-nov11-nov12-nov13-nov14-nov15-nov16-nov17-nov18-nov19-nov20-nov21-nov22-nov23-nov24-nov25-nov26-nov27-nov28-nov29-nov30-nov1-dec2-dec3-dec4-dec5-dec6-dec7-dec8-dec9-dec10-dec11-dec12-dec13-dec14-dec15-dec16-dec17-dec18-dec19-dec20-dec21-dec22-dec23-dec24-dec25-dec26-dec27-dec28-dec29-dec30-dec31-dec
3
4
5
6Betaling adminsQ4
7Communicatie1111444113111444
84
94
10
11
12
130
Data
Cell Formulas
RangeFormula
A13A13=A9+A11+A12
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,212,927
Messages
6,110,693
Members
448,293
Latest member
jin kazuya

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