Sumif (or return) numbers or text

E

Exceltung

New Member
Joined
May 29, 2019
Messages
16
Hi,

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
6-Jan-19​
13-Jan-19​
20-Jan-19​
27-Jan-19​
3-Feb-19​
10-Feb-19​
17-Feb-19​
24-Feb-19​
3-Mar-19​
10-Mar-19​
17-Mar-19​
24-Mar-19​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Line 1Product 1
1​
2​
3​
A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Line 1Product 2A
2​
3​
4​
A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Line 1Product 3A
2​
A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
Line 1
1​
2​
3​
AA
2​
3​
4​
AA
2​
A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Line 1Sumif
1​
2​
3​
0​
0​
2​
3​
4​
0​
0​
2​
0​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>

Currently, if I use sumif (at line 6), Excel return text as "0".
I need Excel to return text as text, example as shown in line 5.
Kindly advice solution.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Excel 2010
ABC
16-Jan-19
2Line 1Product 11
3Line 1Product 2
4Line 1Product 3
5Line 11
6Line 1Sumif1

<tbody>
</tbody>
Sheet1​
Copy C5 And C6 to Columns D5:N6

Worksheet Formulas
CellFormula
C6=IF(ISNUMBER(C5),C5,0)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C5{=IF(OR(ISNUMBER(C2:C4)),SUM(C2:C4),"A")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:
Upvote 0
Hi,

Related problem
Excel 2016 (Windows) 32 bit

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Area

6-Jan-19​

13-Jan-19​

20-Jan-19​

27-Jan-19​

3-Feb-19​

10-Feb-19​

17-Feb-19​

24-Feb-19​

3-Mar-19​

10-Mar-19​

17-Mar-19​

24-Mar-19​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
Area 1

1​

2​

3​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
Area 1

A​

2​

3​

4​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Area 1

A​

2​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
Area 2

A​

1​

2​

3​

4​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Area 2

A​

1​

1​

2​

A​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
Area 2

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
Area 2

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
Summary of Area 1

1​

2​

3​

A​

A​

2​

3​

4​

A​

A​

2​

A​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
Summary of Area 2

A​

1​

2​

3​

4​

A​

A​

1​

1​

2​

A​

A​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>
Table shows two areas (Area 1 and Area 2)
Need Excel to return summary of Area 1 (row 9) and Area 2 (row 10) as shown.
 
Upvote 0
How about

+Fluff.xlsm
ABCDEFGHIJKLM
1Area06-Jan-1913-Jan-1920-Jan-1927-Jan-1903-Feb-1910-Feb-1917-Feb-1924-Feb-1903-Mar-1910-Mar-1917-Mar-1924-Mar-19
2Area 1123A
3Area 1A234A
4Area 1A2A
5Area 2A1234A
6Area 2A112AA
7Area 2
8Area 2
9Summary of Area 1123AA234AA2A
10Summary of Area 2A1234AA112AA
List
Cell Formulas
RangeFormula
B9:M9B9=INDEX(B$2:B$8,MATCH(1,($A$2:$A$8="Area 1")*(B$2:B$8<>""),0))
B10:M10B10=INDEX(B$2:B$8,MATCH(1,($A$2:$A$8="Area 2")*(B$2:B$8<>""),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hi,

I returned here to see the posting but noticed the posting is unreadable.
Any advice?

Thanks in advance.
 
Upvote 0
I have converted it, so it's now readable
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Locked
  • Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
99
Fluff
Fluff
C
  • Question
sumifs WITH eom DOESN'T WORK
Replies
3
Views
109
Peter_SSs
Peter_SSs
S
  • Solved
lookup return from range of dates
Replies
4
Views
82
Peter_SSs
Peter_SSs
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
84
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,375
Messages
6,119,164
Members
448,870
Latest member
max_pedreira

Share this page

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