# Solved how to create sumifs function having nested mid function

May 10, 2017 at 09:06:45
Specs: Windows 7
 Amount Profit Ctr Zone MIS Heading22,841.00 6100 6 c.Other Income1,537.00 6620 6 c.Other Income-351 9000 9 c.Other Income-158,182.00 7600 7 b.Other Operating Revenue158,182.00 61Z0 6 b.Other Operating Revenue-500 4300 4 c.Other Income-500 6630 6 b.Other Operating Revenue-500 3130 3 c.Other Income-500 7220 7 c.Other Income-500 7620 7 c.Other Income-500 61Z0 6 c.Other Income-500 6610 6 c.Other Income-530 7100 7 c.Other IncomeI want sum of amount having zone as 7, having profit ctr as mid(ctr,2,1)="6", mis heading as c.Other incomePl.suggest

See More: how to create sumifs function having nested mid function

#1
May 10, 2017 at 10:45:35
 The SUMIFS() function will not take a MID() function, so you will need to use a SUMPRODUCT() instead,You did not format your data using the < PRE > tags so not completely sure If I have read your data correctly, but it should be something like:=SUMPRODUCT(--(MID(B1:B13,2,1)="6"),--(C1:C13=7),--(D1:D13="c.Other"),A1:A13)Please read this HOW-TO which explains the use of the < PRE > tags to align your data:https://www.computing.net/howtos/sh...MIKEhttp://www.skeptic.com/

Report •

#2
May 10, 2017 at 10:48:30
 Please click on the link at the end of this post and read the instructions on how to post example data in this forum. Then edit or repost your data, using the pre tags and including Column letters and Row numbers as shown in the instructions. Thanks.

Report •

#3
May 10, 2017 at 11:03:40
 If for some reason you don't want to use Mike's excellent SUMPRODUCT solution, SUMIFS could be used with a Helper Column containing the MID function. One of your criteria_range arguments would refer to the Helper Column.

Report •

Related Solutions

#4
May 11, 2017 at 21:36:11
 Thanks Sir the formula working

Report •

#5
May 12, 2017 at 01:53:39
 Create a helper column C:=OR(MID(A2,3,1)="7",MID(A2,3,1)="9")SumIfs function is then:=SUMIFS(B:B,C:C,TRUE)

Report •