How to use Sumif with multiple Rows

[OAhmed]

Hi,

I need help with sumif formula using wildcard criteria, with multiple rows. In the below example, I wish to calculate D01.H231548.365897.2356"*" from APR-OCT

Hope I am clear in what I am asking.

Code	Opening Balance	APR	MAY	JUN	JUL	AUG	SEP	OCT
‭D01.H231548.365897.2356.071043.999999.999999‬	0.00	0.00	0.00	0.00	14,944.19	3,407.80	0.00	3,304.58
‭D01.H231548.365897.2356.071049.999999.999999‬	0.00	0.00	0.00	0.00	12,860.18	3,719.54	0.00	2,493.83
‭D01.H231548.354879.2363.071058.999999.999999‬	0.00	0.00	0.00	0.00	34.00	0.00	0.00	0.00
‭D01.H231548.365897.2356.071055.999999.999999‬	0.00	0.00	0.00	0.00	14,349.83	0.00	4,675.27	3,846.61
‭D01.H231548.365897.2356.071027.999999.999999‬	0.00	0.00	0.00	0.00	9,576.57	2,372.60	0.00	3,401.20
‭D01.H231548.354879.2363.072575.999999.999999‬	0.00	0.00	0.00	0.00	-3.12	-42.85	0.00	0.00
‭D01.H231548.365897.2356.071058.999999.999999‬	0.00	0.00	0.00	0.00	32,333.54	3,927.73	0.00	7,165.81
‭D01.H231548.354879.2363.071055.999999.999999‬	0.00	0.00	0.00	0.00	137.13	0.00	0.00	0.00
‭D01.H231548.354879.2363.072115.999999.999999‬	0.00	0.00	0.00	0.00	73.38	126.12	0.00	10.25
‭D01.H231548.354879.2363.072295.999999.999999‬	0.00	0.00	0.00	0.00	-2,858.60	0.00	-2,423.40	0.00
‭D01.H231548.354879.2363.072371.999999.999999‬	0.00	0.00	0.00	0.00	-108.25	0.00	-24.36	-16.79

Regards,

 

[bosco_yip]

Please upload a sample file as per described and with indicate the criteria as well in helping the readers to reply your query

Regards
Bosco

 

[rahulshewale1]

hii @OAhmed ,


See if is ok ?

PFA Sheet.


Regard
Rahul shewale

 

[OAhmed]

Hi Rahul,

Can't it be done without using total column, just by using sumif. The point is don't want to manipulate the source report in any way.

Thanks.

 

[John Jairo V]

Hi, to all!

An option could be (in @rahulshewale1 's file):

=SUMPRODUCT(ISNUMBER(SEARCH(M6,A$2:A$12))*C$2:I$12)

Blessings!

 

[rahulshewale1]

hii @OAhmed ,


See if is ok ?

Power Query Solution



Regard
Rahul shewale

 

[OAhmed]

Hi John,

Thanks it works perfectly. Cheers.

@Rahul - Thanks for you help.