IF(and([New field2]="carBrand",[Spend]) +
[New field2]="PureBrand",[Media Spend Car])

This is the formula I though but its not working any suggestion will be greatly appreciated???

Do I ave to use IF else or something else, it seems to do something but not in a way I want... please guide me, its my final batch

Nicci: Your formulas need to evaluate row by row. If you are are testing for [Newfield2]='CARBRAND' then you cannot also test for [Newfield2]='PUREBRAND' ...both things can never be true for the same row, unless you want to tokenise the field and permit multiple values per cell...?

SUBSET functions can process groups of rows with the same value in a given field, but in your data set, you have logic trying to group more than one value for [Newfield2].

I think we need to do some more pre-processing to the data set such that you do not need to test [Newfield2] twice for different values?

I have created few extra column to avoid such thing and guys in work seems ok so far.. This callc actually shows them overall comparison and really want this in the report.

Your Field organiser formula is adding values of 2 cells, from fields [Total spend] and [Media spend] in the same row. A 'vertical' sum is performed when you aggregate data.

If you wish to have different total for each data subset, depending on the value of another field, e.g. to calculate sum for each data Category, then you need to use subset formulas, such as:

SUBSET_SUM([Test], SUBSET([Category]))

The sum of the [Test] field, for the data subset with the same [Category] field value as the record being evaluated.

The formula needed here was
IF([New field2]="PureBrand",
SUBSET_SUM ([Spend],SUBSET([New field2],"CarBrand"))+
SUBSET_SUM ([Media Spend Car],SUBSET([New field2],"PureBrand")),
null)