How SUMIFS will be used in this example?

mekansiz

New Member
Joined
May 13, 2022
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hello Friends,

In this example, I couldn't figure out how to use SUMIFS (Çoketopla) formula. Let me explain what I want. As you see, the formula works when there is only the same word like "TOPRAK", "BETON", "KORUGE". On the other hand, I would like to give the "BORDÜR" and "PARKE", which appear separately in the table above, together as "BORDER-PARKE" in the summary table below. Of course, I can manually pick and collect one by one. But how can I use SUMIFS, which is a short cut? I'll be happy if you can help me.


1652486974642.png
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
It doesn't look like your criteria is not an exact match to your lookup field in column L. I am thinking you will need to use VBA and the Instr function to make this work.
 
Upvote 0
Thank you, but that job takes a lot of work. So keep picking and collecting by hand one by one.
 
Upvote 0
If you re-upload your data with XL2BB instead of a picture, I would be inclined to offer some code. I cannot manipulate data in a picture and really, I am too lazy to try and recreate your data to solve your issue. If you are unfamiliar with XL2BB, look at my signature. There may also be a Power Query solution once I see some real data.
 
Upvote 0
If you re-upload your data with XL2BB instead of a picture, I would be inclined to offer some code. I cannot manipulate data in a picture and really, I am too lazy to try and recreate your data to solve your issue. If you are unfamiliar with XL2BB, look at my signature. There may also be a Power Query solution once I see some real data.
You are so helpful that I can't explain. In my free time, I will share the file in the format you want. However, there is a situation. Doesn't this VBA you write work well for tables that will be used for similar purposes later on?
 
Upvote 0
Here it is. Thank you for your precious time.

2022 İhaleler YM Çalışması.xlsx
ABCDEFGHIJKL
2Sıra Noİş Kalemi Noİş Kaleminin Adı ve Kısa AçıklamasıÖlçü BirimiMiktarıYMYM TutarıMinMaxSonuçOrtalama
32.50038,2595.636,202,393,993,190319033,19TOPRAK
45.00022,25111.225,802,784,643,710371043,71TOPRAK
53.5008,5229.830,100,751,240,995099510,995BORDÜR
61.867,2062,14116.022,602,94,843,870387043,87TOPRAK
71.20024,3629.230,500,731,220,975097510,975DİĞER
81.200273,57328.281,008,2113,6910,951095110,95KORUGE
92443,721.049,300,030,040,03500350,035KORUGE
1067,21.423,1695.636,202,393,993,190319033,19BETON
112,31.238,302.848,100,070,120,09500950,095BETON
124.68082,19384.643,409,6216,0412,831283112,83BORDÜR
132.730117,01319.436,907,9913,3210,656065610,655PARKE
141.92055,35106.279,102,664,433,545354543,545TOPRAK
15400167,8967.155,201,682,82,240224022,24ASFALT
1616.50073,171.207.294,6030,250,3440,274027440,27ASFALT
171.21584,88103.131,202,584,33,440344033,44TEMEL ALTTEMEL
18TOPLAM TUTAR(K.D.V Hariç)2.997.700,2074,9812599,99
19
20Tahmini YM:2.997.700,202.998.000,00
21
22YM YE GÖRE İCMAL
23TOPRAK429.163,7014,32
24BETON98.484,303,29
25KORUGE329.330,3010,99
26BORDÜR-PARKE733.910,4024,48
27ALTTEMEL-TEMEL-ASFALT1.377.581,0045,95
28DİĞER29.230,500,98
29TOPLAM2.997.700,20100,00
30
392952 YM
Cell Formulas
RangeFormula
F3:F17F3=G$20*K3/E3/100
G3:G17G3=E3*F3
J3:J17J3=G3/$G$18*100
G18:I18,K18G18=SUM(G3:G17)
K3:K17K3=AVERAGE(H3:I3)
H23:H25,H28H23=SUMIFS($G$3:$G$17,$L$3:$L$17,E23)
J23:J29J23=H23/$H$29*100
H26H26=SUM(G12:G13,G5)
H27H27=SUM(G15:G17)
H29H29=SUM(H23:I28)
 
Upvote 0
How about
Excel Formula:
=SUM(SUMIFS($G$3:$G$17,$L$3:$L$17,{"BORDÜR","PARKE"}))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top