CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Joao S
Joao S

Reputation: 15

Add values to a sum in each multiple criteria

So the formula bellow is working fine and it's searching in a named range for multiple keys and counting how many exist of each one in that named range and then multiplying it for a specific value, for example:

A B C D E F
1 - Day 1 Day 2 Day 3 Day 4 Total
2 Jan F B F F 11
3 Feb B B 4

I'm just going to show code in F2 because F3 is the same just with a different named range

INDIRECT($A2) -> This is a named range there's multiple of them
'Info'!$B$9 -> F
'Info'!$B$10 -> B
'Info'!$C$9 -> 3
'Info'!$C$10 -> 2

=SUM(
        COUNTIFS(INDIRECT($A2);'Info'!$B$9)*'Info'!$C$9;
        COUNTIFS(INDIRECT($A2);'Info'!$B$10)*'Info'!$C$10;
)

What I would like to do now is having a second row (3) bellow where I can add extra values to the count and make it add that values on that specific key above so the multiplication is done correctly for example:

A B C D E F
1 - Day 1 Day 2 Day 3 Day 4 Total
2 Jan F B F F 26
3 JanExtra 1 6 15
4 Feb B B 4
5 FebExtra 0

Any idea of how can I accomplish that with the code above? I'm actually stuck here and not having any ideas, thanks in advance.

Upvotes: 0

Views: 46

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(REGEXMATCH(A2:A; ".*Extra"); 
 IFERROR(1*FLATTEN(SPLIT("♀♂"&MMULT(FILTER(B2:AF; REGEXMATCH(A2:A; ".*Extra"))*
 FILTER(IFNA(VLOOKUP(B2:AF; Info!A2:B; 2; 0)); NOT(REGEXMATCH(A2:A; ".*Extra"))); 
 TRANSPOSE(COLUMN(B:AF))^0); "♂")); 0); 
 MMULT(IFNA(VLOOKUP(B2:AF; Info!A2:B; 2; 0); B2:AF)*1; TRANSPOSE(COLUMN(B:AF))^0)+
 IFERROR(1*FLATTEN(SPLIT(MMULT(FILTER(B2:AF; REGEXMATCH(A2:A; ".*Extra"))*
 FILTER(IFNA(VLOOKUP(B2:AF; Info!A2:B; 2; 0)); NOT(REGEXMATCH(A2:A; ".*Extra"))); 
 TRANSPOSE(COLUMN(B:AF))^0)&"♂♀"; "♂")); 0)))

enter image description here

Upvotes: 1

Related Questions