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 Heading
22,841.00 6100 6 c.Other Income
1,537.00 6620 6 c.Other Income
-351 9000 9 c.Other Income
-158,182.00 7600 7 b.Other Operating Revenue
158,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 Income


I want sum of amount having zone as 7, having profit ctr as mid(ctr,2,1)="6", mis heading as c.Other income
Pl.suggest


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

Report •

#1
May 10, 2017 at 10:45:35
✔ Best Answer
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...

MIKE

http://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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •

Ask Question