Summarizing information SUF IF and VLOOKUP

Hi Experts,
I have attached here my actual work sheet which I used to do some "summations" of information and VALUES.

Just today, I saw that the "total qty" in the 'Data Sheet' doesn't match to the 'Summary Sheet.
When I did some re checking manually, I found that the information highlighted in RED has not been summarized/added.
I have checked formulas, but, I don't find any errors.

If someone can help, it is great.

Regards
Anuruddha

I did as you explained. However, when I searched named range manger, I didn't find clear named ranges!!. Inside named range manager, there are few named ranges which I am not clear about. Can you pls correct the attached work sheet and send me?

Originally Posted by Bo_Ry

The formula name range Booking_Ref
=OFFSET('Data Sheet'!$D$2,,,COUNTIF('Data Sheet'!$D$2:$D$1055,"*?"),1)

"*?" is only count Text
it misses 4 count for these number
21770
21760
21760
21166

Please change to
=OFFSET('Data Sheet'!$D$2,,,COUNTA('Data Sheet'!$D$2:$D$1055))
or
=OFFSET('Data Sheet'!$D$2,,,SUM(COUNTIF('Data Sheet'!$D$2:$D$1055,{"*?",">0"})))

I think this is not the case. These values are already there in the summary sheet.

Originally Posted by Bo_Ry

Please see differences from your sheet on the left and post#2 sheet
37 and 41 Entry
Countif( ,"*?) and Counta( )

Attachment 617651

Yes. The solution is right. Anyway, the work sheet I attached at the beginning of the post, this below issue was not found;

*?" is only count Text
it misses 4 count for these number
21770
21760
21760
21166

I found only the RED highlighted booking references!!. I am confused.
Anyway, thanks a lot for the advise and solution. I will do some more works outs, and let you know.

Originally Posted by Anuru

Yes. The solution is right.

That was just an answer, the solution is in post #3

Re:- confusion with countif in named range, consider the following in A1:A3

ABC
123
DEF

=COUNTIF(A1:A3,"*?") will count 2 (A1 and A3)
When you use that as the height of =OFFSET(D1,0,0,COUNTIF(A1:A3,"*?"),1) you are saying you want 2 rows (the result of countif) which gives you D1:D2, not D1 and D3 as you might think.
This is what happened in your sheet, because you had 4 numbers that were not counted, OFFSET lost the bottom 4 rows of data.

Hi,
I faced another problem today due to an strange behavior of the work sheet. Normally, I copy data directly from another work sheet (filtered data using filter options) to this sheet. It was working fine and the copied data (filtered) were pasted on the "Data Sheet" tab as per the column headings, without any issue.

However, today, when I try to copy and paste data from the same data sheet to this work sheet, then, the copied data don't paste properly. This issue starts from column D and on wards. I din't experience this issue before. Now, the copied data get pasted very irregularly leaving some rows empty and also missing information etc.

I have added the copied data in a new sheet called "sheet 1, which were the data copied from original sheet (the original master sheet is big in size and cannot be attached here).

You can try copying data from Sheet 1 to "Data Sheet" in keeping with the column headings on both the sheets to check how the error is re produced. For example, if you copy "Booking reference" from sheet 1 and try to copy it to Data sheet, you can see how the data at the bottom of the data sheet move.

Pls kindly see the why this issue cropped up suddenly..

Please see post #3 for the correct way to fix your previous problem. As you completely ignored it in favour of a less favourable suggestion, I am loathe to waste my time providing you with additional assistance, however, given that the file attached to my post has never been downloaded, I'm going to assume that it was an oversight more than ignorance.

As with your previous issue, the problem lies with incorrect use of the structured tables in your file (a structured table is when you apply a table from the 'Insert' tab of the excel ribbon, as has been done in your file). When you have this, you should not have any empty rows at the bottom of the table. As you add new data it will automatically resize to cover it.

Because your table is not the correct size for the data that you are copying into it the structure is falling apart.

I've re-attached the copy of your file with my previous suggestions for improvement, along with another slight change to the formulas in the summary to further improve calcualtion efficiency.

If you look at the Data sheet, you will notice that I have deleted all rows except for the first one. If you copy your data and paste into row 2 of the relevant column, you will notice that the table resizes to fit the data.

It looks like you have to copy and paste a few columns at a time, once the first is done as above and the table is the correct size, you should be able to do the rest without problems.

If you are going to delete the data from the sheet to re-use it again later, you need to right click and 'Delete Entire Row' or 'Delete Table Rows' (whichever option is given to you ).
If you select the cells and press the delete key, you will probably run into the same problem again!!

This whole process could probably be simplified with power query.

Originally Posted by Anuru

Actually, I worked out as per your advice in post 3, but was not successful and I thought no to bother you again.

It is no bother when you ask for additional guidance if you have problems in doing or understanding something that has been suggested, in fact it is quite the opposite because it shows that you are willing to put in some effort to resolving your problem rather than letting somebody else do it for you.

When you ask a question and receive multiple answers suggesting different ways to solve your problem, remember that not all answers are solutions!

An answer does what you need, but may not be the best or most efficient method, it may work now but not later (as you have found here).
A solution should do what you need efficiently and be robust enough to work without failing at a later stage.

Originally Posted by jason.b75

It is no bother when you ask for additional guidance if you have problems in doing or understanding something that has been suggested, in fact it is quite the opposite because it shows that you are willing to put in some effort to resolving your problem rather than letting somebody else do it for you.

When you ask a question and receive multiple answers suggesting different ways to solve your problem, remember that not all answers are solutions!

An answer does what you need, but may not be the best or most efficient method, it may work now but not later (as you have found here).
A solution should do what you need efficiently and be robust enough to work without failing at a later stage.

Yes, I understand completely. Due to the busy working environment (working as General Manager Planning for an apparels manufacturer), full of e-mails, calls, meetings, floor visits etc, studying something a bit deeply has become a bit difficult.

Can we add a formula column to the data sheet?

Hope a Helper Column, is it? Of course, no issue at all.

This is best I can do, the dates are in reverse order though.

I can change it so that they are in the correct order but it will probably double the already slow calculation times.

This is great, though it takes a little time to process. I checked randomly some figures, and I didn't find any calculation errors.
The data don't need to be in ascending order because, I am using sorting/slicers to filter these data. Attached work book for your reference (the same data from the above work sheet have been copied without formulas).

I'll have another look at it for you when I have a bit more free time, see if I can speed it up a bit. Might not be for a week or so though.

Great. Thanks.