Array Formula Results & Value Issue

Killsek

New Member
Joined
Mar 1, 2016
Messages
6
Hello,

I am stuck on a problem I am having with an Array formula's results. I have a Data sheet which has 4 columns I am using to get the data from and populated on a JAN sheet. All is working but even the the value displays a number it doesn't act like a number. I have verified all the numbers in the DATA sheet are numbers but the formula produces a non number value even though it displays the correct number.

1650689548893-png.63075


1650689850068.png


The array formula that I am using to populate the fields is: {=IFERROR(INDEX((DATA!$G$2:$G$20 & DATA!$H$2:$H$20), SMALL(IF(N$4=DATA!$A$2:$A$20, ROW(DATA!$A$2:$A$20)-ROW(DATA!$A$2)+1), ROW(1:1))),"" )}

One of the issues I am having is on a Summary sheet I am trying to do is look up a certain field on the JAN tab and give the SUM of all it finds titled with the name : =IFERROR(SUMIF(Jan!$B$4:$O$69,$A4,Jan!$C$4:$O$69),"")
In this case A4 is text "Wages and Salaries"

1650690615587.png


How it works is the Jan sheet is a Calendar and each day/box has 10 rows and 2 columns. the first column pulls the category from the DATA sheet based on the days date the populates the fields within that day with what it found and the next column is the credit or debit from the DATA sheet.

Is there an easier way to do this; Or have I really made a mountain out of a mole hill? Any help is appreciated.!
 

Attachments

  • 1650689548893.png
    1650689548893.png
    39.4 KB · Views: 32

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
With this:
DATA!$G$2:$G$20 & DATA!$H$2:$H$20
the value could be: 200 & "" = "200"
but its text string followed by a "" , not value

Why not try:
DATA!$G$2:$G$20 + DATA!$H$2:$H$20

???
 
Upvote 0
Solution
With this:
DATA!$G$2:$G$20 & DATA!$H$2:$H$20
the value could be: 200 & "" = "200"
but its text string followed by a "" , not value

Why not try:
DATA!$G$2:$G$20 + DATA!$H$2:$H$20

???
Dang, you're awesome; thank you so much....can't believe it was that simple
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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