Selecting specific text and suming the results.

[mozahra]

Dear All,

Thanks in advance for your time to help me with my inquiry.

I have some expenses data where I want to select common or close text from a list and sum the amount spent for a specific month "Mar". Then I will work on creating a dashboard out of it.


Example of output required.

ENOC SITES : 140 ARE. [ Data below is having different Site numbers, but I want to group them all together]
SHARJAH CO-OP : 260 ARE.
[TABLE="width: 568"]
<tbody>[TR]
[TD]9-Mar-17
[/TD]
[TD]ENOC SITE 1032 DUBAI UAE
[/TD]
[TD]ARE
[/TD]
[TD]95.01
[/TD]
[/TR]
[TR]
[TD]8-Mar-17
[/TD]
[TD]SHARJAH CO-OP, HALWAN SHJ ARE
[/TD]
[TD]ARE
[/TD]
[TD]16.95
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL ZAJIL FAIRS MANAGEM SHARJAH ARE
[/TD]
[TD]ARE
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL ZAJIL FAIRS MANAGEM SHARJAH ARE
[/TD]
[TD]ARE
[/TD]
[TD]90.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]ADNOC AL ASRAR ABU DHABI ARE
[/TD]
[TD]ARE
[/TD]
[TD]114.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]SHARJAH CO-OP, HALWAN SHJ ARE
[/TD]
[TD]ARE
[/TD]
[TD]73.55
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL SHAAB VILLAGE-AL ZA SHARJAH UAE
[/TD]
[TD]ARE
[/TD]
[TD]50.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL SHAAB VILLAGE-AL ZA SHARJAH UAE
[/TD]
[TD]ARE
[/TD]
[TD]40.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL SHAAB VILLAGE-AL ZA SHARJAH UAE
[/TD]
[TD]ARE
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD]6-Mar-17
[/TD]
[TD]AL SHAAB VILLAGE-AL ZA SHARJAH UAE
[/TD]
[TD]ARE
[/TD]
[TD]55.00
[/TD]
[/TR]
[TR]
[TD]5-Mar-17
[/TD]
[TD]AL MANAMA H/MARKET-BR DUBAI ARE
[/TD]
[TD]ARE
[/TD]
[TD]63.90
[/TD]
[/TR]
[TR]
[TD]4-Mar-17
[/TD]
[TD]ENOC SITE 1036 DUBAI UAE
[/TD]
[TD]ARE
[/TD]
[TD]112.01
[/TD]
[/TR]
[TR]
[TD]3-Mar-17
[/TD]
[TD]SHARJAH CO-OP, HALWAN SHJ ARE
[/TD]
[TD]ARE
[/TD]
[TD]100.80
[/TD]
[/TR]
[TR]
[TD]2-Mar-17
[/TD]
[TD]GIFTS MARKETS GEN.TRAD SHARJAH ARE
[/TD]
[TD]ARE
[/TD]
[TD]49.50
[/TD]
[/TR]
[TR]
[TD]1-Mar-17
[/TD]
[TD]SHARJAH CO-OP, HALWAN SHJ ARE
[/TD]
[TD]ARE
[/TD]
[TD]142.40
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for your support and efforts.

 

[Joe4]

Welcome to the Board!

Can you tells us what the expected results of your example is, and explain the logic behind it?

Perhaps you are looking for the SUMIF or SUMIFS formulas.
See:
https://www.techonthenet.com/excel/formulas/sumif.php
and
https://www.techonthenet.com/excel/formulas/sumifs.php

 

[mozahra]

Thanks Joe4.

I want Excel to through the column that has the text and pick all text that are common, for instance ENOC / Sharjah / AL SHAAB and sum all the spent amount together. Means the output will be

ENOC SITES : 140 ARE
Sharjah : 2000 ARE.
AL ZAJIL : 350 ARE.

Thanks for your support and I hope am clear enough.
awaiting your kind response.

Regards

 

[63falcondude]

ENOC SITES : 140 ARE
Sharjah : 2000 ARE.
AL ZAJIL : 350 ARE.

I'm not sure where these numbers are coming from. Is this what you are looking for?

Excel 2010
	A	B	C	D	E	F	G
1	9-Mar-17	ENOC SITE 1032 DUBAI UAE	ARE	95.01		ENOC SITE	207.02
2	8-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	16.95		Sharjah	333.7
3	6-Mar-17	AL ZAJIL FAIRS MANAGEM SHARJAH ARE	ARE	100		AL ZAJIL	190
4	6-Mar-17	AL ZAJIL FAIRS MANAGEM SHARJAH ARE	ARE	90
5	6-Mar-17	ADNOC AL ASRAR ABU DHABI ARE	ARE	114
6	6-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	73.55
7	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	50
8	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	40
9	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	100
10	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	55
11	5-Mar-17	AL MANAMA H/MARKET-BR DUBAI ARE	ARE	63.9
12	4-Mar-17	ENOC SITE 1036 DUBAI UAE	ARE	112.01
13	3-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	100.8
14	2-Mar-17	GIFTS MARKETS GEN.TRAD SHARJAH ARE	ARE	49.5
15	1-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	142.4
Sheet1

Cell Formulas
Range	Formula
G1	=SUMIF(B:B,F1&"*",D:D)

 

[mozahra]

Thanks Dear,
As I motioned they are just examples. you got it right. If I want excel to consider either EONC / ADCO (Name of petrol stations) text. How would I do this as per your formula.

 

[63falcondude]

If I want excel to consider either EONC / ADCO (Name of petrol stations) text.

I don't know what this means. In your example, I do not see "EONC" or "ADCO".

 

[mozahra]

Hi 63Falcondude,
My bad, its a typo, I mean ENOC/ ADNOC.

 

[63falcondude]

Hi 63Falcondude,
My bad, its a typo, I mean ENOC/ ADNOC.

Are you looking for this?

Excel 2010
	A	B	C	D	E	F	G
1	9-Mar-17	ENOC SITE 1032 DUBAI UAE	ARE	95.01		ENOC	207.02
2	8-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	16.95		ADNOC	114
3	6-Mar-17	AL ZAJIL FAIRS MANAGEM SHARJAH ARE	ARE	100
4	6-Mar-17	AL ZAJIL FAIRS MANAGEM SHARJAH ARE	ARE	90
5	6-Mar-17	ADNOC AL ASRAR ABU DHABI ARE	ARE	114
6	6-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	73.55
7	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	50
8	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	40
9	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	100
10	6-Mar-17	AL SHAAB VILLAGE-AL ZA SHARJAH UAE	ARE	55
11	5-Mar-17	AL MANAMA H/MARKET-BR DUBAI ARE	ARE	63.9
12	4-Mar-17	ENOC SITE 1036 DUBAI UAE	ARE	112.01
13	3-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	100.8
14	2-Mar-17	GIFTS MARKETS GEN.TRAD SHARJAH ARE	ARE	49.5
15	1-Mar-17	SHARJAH CO-OP, HALWAN SHJ ARE	ARE	142.4
Sheet1

Cell Formulas
Range	Formula
G1	=SUMIF(B:B,F1&"*",D:D)

If not, please show us an example of what it is that you are looking for using the data the you have already provided.

 

[mozahra]

Dear 63falcon,
Thanks once again for your support. I have filtered the data I have previously given to show more what I would like Excel to do for me. I would like Excel to get the sum of all ADNOC or ENOC petrol stations only from the given data.

Regards.
[TABLE="class: outer_border, width: 785"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9-Mar-17[/TD]
[TD="align: left"]ENOC SITE 1032 DUBAI UAE[/TD]
[TD="align: left"]ARE[/TD]
[TD]95.01[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6-Mar-17[/TD]
[TD="align: left"]ADNOC AL ASRAR ABU DHABI ARE[/TD]
[TD="align: left"]ARE[/TD]
[TD]114[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4-Mar-17[/TD]
[TD="align: left"]ENOC SITE 1036 DUBAI UAE[/TD]
[TD="align: left"]ARE[/TD]
[TD]112.01[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Petrol Station Expenses(ADNOC + ENOC)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]321.02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="5"></colgroup>[/TABLE]

 

[63falcondude]

Oh so you want to take the sum for ADNOC and ENOC. In that case, try this:

=SUM(SUMIF(B:B,{"ENOC*","ADNOC*"},D:D))