Add Cell base on another cell text

[Ricky_J]

Hello everybody,

Probably an easy question for everybody here.
I want to add multiple cells base on another cell text.

For example I want to add D7 if B8 says UTO with H7 if J8 says UTO.
I dont want D7 to add up with H7 if another text is included in D8 or J8.

=COUNTIF(D7=if=(B8="UTO"),H7=if(F8="UTO"))

I know the formula is incorrect, that's were I need you guys help.

Thank you

 

[Joe4]

I am afraid that your question is not quite clear.

Can you post a few examples of what is in D7, H7, B8, and F8, along with what you expected results in each case should be?

 

[Ricky_J]

I want to be able to add D7,H7,L7,P7,T7 only if B8,F8,J8,N8,R8 have a Text entry for example UTO and show the SUM on AH7
D7 would be paired with text in B8
H7 with F8
L7 with J8
P7 with N8
T7 with R8

If another text (PTO for example) is entered in F8, I dont want H7 to be added to D7,L7,P7,T7


https://drive.google.com/open?id=1q6TUDy8RFtcYFpL2Vus8tT3VMq1q3aTq

 

[Joe4]

Try this:

=SUMPRODUCT(--(B8:R8="UTO"),--(MOD(COLUMN(D7:T7),4)=0),--(D7:T7))

This sums the values in those cells in row 7 you referenced if their corresponding values in row 8 are "UTO".

 

[Ricky_J]

That's it.
My skills are very basic. I'll just have to learn how to decode this formula for the future.
Thank you very much.
Your help is much appreciated.

 

[Joe4]

If row 8, if the "UTO" values would only ever appear in those four columns you mentioned, you could get away with a simpler SUMIFS formula like this:

=SUMIFS(D7:T7,B8:R8,"UTO")

But if the following situation could exist:
- a value of "UTO" in cell C8 (not one of your mentioned columns)
- a numeric value in cell E7 (not one of your mentioned columns)
That value would be picked up by the SUMIFS formula (when you do not want it to).

SUMPRODUCT can be use in place of any COUNTIF, COUNTIFS, SUMIF, or SUMIFS formula, but has more flexibility than those.
(for a write-up on SUMPRODUCT and how that all works, see: https://www.ablebits.com/office-add...duct-function-formula-examples/#Conditionally)

The SUMIFS formula above I listed would look like this as a SUMPRODUCT formula:

=SUMPRODUCT(--(B8:R8="UTO"),--(D7:T7))

So, all that I did was add another condition to that SUMPRODUCT.

MOD(COLUMN(D7:T7),4)=0

While kind of daunting looking at first, it isn't so bad when you see what is going on.
The columns you wanted to check, D, H, L, P and T, are the 4th, 8th, 12th, 16th, and 20th columns respectively.
What do they all have in common? They are all multiple of 4!

The Excel COLUMN function return the column number for any cell. For example:
=COLUMN(D7)
would return 4
So we can use that function to get the column number of the cells we are working across.

The MOD function gives us a remainder when the first number is divided by the second.
So in the formula, we are dividing the column number by 4 to see what our remainder is.
Since all the columns we want to consider are multiples of 4, their remainders when divided by 4 is 0.
So this condition ensures that we are only include columns that are multiples of four.

I hope that helps clarify things a little.

 

[Ricky_J]

It does thank you very much.
But what if the columns I want to add are no longer multiple of 4, how could i implement that in the rule?
For example what if I wanted to add E7, I7,M7,Q7 and U7
Instead of D7,H7,L7,P7,T7?
They no longer share a commun denominator since it would be column 5, 9, 13, 17 and 21.

 

[Joe4]

They no longer share a commun denominator since it would be column 5, 9, 13, 17 and 21.

It still works, since the "jumps" are still 4. Remember, the MOD function returns the remainder.
When divided by 4, all those numbers have the same remainder (namely, 1).
So instead of this:

MOD(COLUMN(D7:T7),4)=0

you would have this:

MOD(COLUMN(E7:U7),4)=1

 

[Ricky_J]

Getting it slowly but surly