Functions to filter unclear data effectively

Hi guys,

I currently have data that I extract and the extracted data is all over the place which doesn't help and I would like to make sense of it in a better way and plot on a graph. At the moment, I'm sorting it manually. Are there good functions and formula for me to sort the data out even if more data is added? I would like it to maybe look something like Sheet2. To make things worse, the refurbished & trial data is from the same location so I would like to formula to have an OR input as well. So IF refurbished OR trial then...

Many thanks

Mufeed

Try this in C4 of the Overall table:

=SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,"*"&C$3&"*",Sheet1!$A:$A,">="&$B4,Sheet1!$A:$A,"<"&$B4+1)

then copy across and down. I think you will need to split the column Trial/Refurbished into two separate columns of Trial and Refurbished. Also, you need to tidy up your data, as you have both M6 A and M6 Quad A, as well as M6 Quad C which is not the same as in your summary table.

Hope this helps.

Pete

That's great. Works really well. Apart from having to separate the trial & refurbished. Is there an easier way to do this?

Also currently I am manually typing in the dates into the 'overall' sheet. What function can I use to take the data from 'Sheet1' automatically. And how can I adjust the formula so that any data that does not exist is not returned as 0 and instead is a blank cell as the 0 shows up on graphs.

Thanks.

For that column only you could have two SUMIFS functions in the same formula - one looking for "*Trial*" and the other looking for "*Refurbished*", instead of the term "*"&C$3&"*".

To avoid 0 showing up on graphs, you should return an error instead, and the basic approach would be:

=IF( your_existing_formula = 0, NA(), your_existing_formula )

In your table you would need to apply conditional formatting so that if a cell contained an error then you would use a white font on a white background, so that the cell would appear blank.

Hope this helps.

Pete

Hi Pete,

I have tried the formula as below for the two SUMIFS:

=SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&Trial&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1)AND(SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&Refurbsished&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1). However it does not work

I've also tried the error approach in replacing 0:

=IF(SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&D$3&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1)=0,NA(),(SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&D$3&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A)))

But it returns 'too few arguments for the function'.

Please advise.

You must have changed the file, as the column references are different. Assuming you have the correct columns, the first formula should be:

=SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*Trial*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1) + (SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*Refurbished*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1)

(changes in red).

In the second formula you have used too many brackets and missed a bit off the end. Try it like this:

=IF(SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&D$3&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1)=0,NA(),SUMIFS(Sheet1!$F:$F,Sheet1!$E:$E,"*"&D$3&"*",Sheet1!$A:$A,">="&$C4,Sheet1!$A:$A,"<"&$C4+1))

Hope this helps.

Pete

Thanks Pete. That now works a treat.

Is there a function to look at the date column and pick out the individual dates instead of a manual input?


Many thanks for your help this far.

Regards,

Mufeed

I'm going away for a few days tomorrow, so I'll have to leave this until I get back next week. Maybe someone else will chip in.

Pete