Need help with sumif cells containing both text and number

[kirkmaciano]

Hi all

I am facing a sumifs problem.

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		4​	4​	4​	4​	5​	5​	5​	5​	5​
2​		27 Apr​	28 Apr​	29 Apr​	30 Apr​	1 May​	2 May​	3 May​	4 May​	5 May​
3​	Person A​
4​	Person B​			F12​	F25​
5​	Person C​							H​	H​	H​
6​	Person D​			H5​
7​	Person E​
8​	Person F​				F30​	F30​	F30​
9​	Person G​	F​	F​	F​
10​	Person H​
11​	Person I​

The outcome I want is Person B returns 37, Person D returns 5, Person F returns 30 under April, and Person F returns 60 under May.

I managed to

i) =SUM(VALUE(RIGHT($B$3:$J$3,LEN(B3:J3)-1))) to sum only the numbers in a row
ii) =SUMIFS(XLOOKUP(lookup_value,$A$3:$A$11,$B$3:$J$11),$B$1:$J$1,MONTH(one of the cells showing a date)) to find the correct row and columns

But I am lost how to sumifs only the numbers after finding the correct row and columns.

Can any experts help me out?

 

[Hui]

Can you please attach a file for us to use

 

[kirkmaciano]

Hi Hui

Thanks for the response. I have attached a file.

If I select Mabel at B19, I would like to see 60 (sum of the numbers from C10:M10 where name is Mabel) at C19 (April) and 60 at D19 (May).

Similarly if I select Mary, I would like to see 30 under April and 30 under May.

The rest of the names should return 0 since there are no numbers in the cells C4:M14.

 

[AlanSidman]

Here is a possible Power Query solution for you. Note that the presentation is different from what you asked.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Attribute", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Date.MonthName(_), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Month Name",{{"Value.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Value.2"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Column1", "Attribute"}, {{"Total", each List.Sum([Value.2]), type nullable text}})
in
    #"Grouped Rows"

 

[bosco_yip]

In C19, enter array formula then copied across right and down :

=SUM(IFERROR(($C$2:$M$2=MONTH(0+("1/"&C$18)))*($B$4:$B$14=$B19)*MID($C$4:$M$14,2,99),0))

Array formula to be confirmed in pressing "Ctrl"+"Shift"+"Enter" instead of just Enter.

 

[John Jairo V]

Hi to all!

Another option could be:

No CSE option:
=SUMPRODUCT(($B$4:$B$14=$B19)*(MONTH(1&C$18)=$C$2:$M$2),--(0&MID($C$4:$M$14,2,15)))

Blessings!