Sum by colour and 2 other parameters.

[AaronDear]

I have found a sumcolor function which I have added into Excel through VBA which works fine, but I am now after a sum where the answer needs to fill 3 conditions including colour.

I need it to sum all the numbers that are in a certain coloured cell from a range of columns if they also meet conditions in 2 other columns. I have attached a simplified version of what i need to do.

I have no VBA experience, so all the help is appriciated and I bhope this makes sense.

Cheers

[rylo]

Hi

See how this goes.

Please Login or Register  to view this content.

Using your example file, it would be called with =myfunc(C3,A3:G10,A3,B3) for a yellow cell, and =myfunc(D3,A3:G10,A3,B3) for a red cell.

rylo

[AaronDear]

Cheers Rylo, Have put in the function and it works fine on the example I gave you, however I think I over simplified the example I gave you!! (My fault) I have attached the exact data I need to look at. I need a function that will sum by colour and 2 other parameters even if there is other data between the parameters. An example using my attachment would be to sum all the pinky coloured cells (sorry colour blind!) where it also equals "C" in column A and "HG" in column F (the answer would be 12). Hope that makes sense. Appriciate your help.

Aaron

[rylo]

Hi

OK, try this one.

Please Login or Register  to view this content.

It would be called with

Please Login or Register  to view this content.

rylo

[WHER]

Alternative, this function would be called with:

Please Login or Register  to view this content.

[AaronDear]

Rylo, Macro has worked fine on all but a couple of queries. It does not seem to like "<>B" as below or any <>. It will work with "B" but not with the does not equal.

=myfunc(AJ$105,$AJ$4:$BR$103,"C",I:I,"<>B",$AH:$AH)

Any ideas?

Much appriciated.

Aaron

[rylo]

Aaron

Original requests all related to having something that was equal to an item. No mention of "not equal" requirements.

Can you put up an updated reference file that will cover the above scenario and any other that you may require. For example are you going to require the greater than / less than options?

rylo

[AaronDear]

Aaron

Original requests all related to having something that was equal to an item. No mention of "not equal" requirements.

Can you put up an updated reference file that will cover the above scenario and any other that you may require. For example are you going to require the greater than / less than options?

rylo

I have attached the reference file. I orginally requested a function that will sum by colour and 2 other parameters even if there is other data between the parameters. I just assumed I could then use "does not equal" argument.
An example using "does not equal" on my attachment would be to sum all the pinky coloured cells (still colour blind!) where it equals "C" in column A and does not equal "WB" in column J (the answer would be 11).
I don't need greater than or less than for any other arguments.
thanks for help with this.

Aaron

[rylo]

Hi

Is the = / != toggle only required for the second condition, or could it also be required for the first condition, or possibly both?

rylo

[AaronDear]

Hi

Is the = / != toggle only required for the second condition, or could it also be required for the first condition, or possibly both?

rylo

Given the opportunity it would be better to have both as it would make the function more flexible for future argumnts.

Cheers

Aaron

[rylo]

Hi

This is really, really ugly, and I'm sure there has to be a better way to evaluate the operators, but I can't think of one. But I think this works.

Please Login or Register  to view this content.

It is called like

Please Login or Register  to view this content.

Where you have to nominate the operator that you want. You only get 2 options: = and <>.

rylo