Complicated COUNTIF (?) with multiple factors

[daniel.d]

Hello there

My laptop is on the brink of being destroyed, on account of Excel not cooperating with me. I'd love some assistance with this one. I have an inventory system which is quite manual, and currently I spend time manually adding up T-Shirt sales by date, style, colour and size. From my point of sale system I get one extract of data, and then my inventory sheet tally's everything up by these factors. In the attached example, AJ4 on "Test Inventory.." should calculate on which specified date did I sell the staple tee in the colour white and size small. An added factor as well is that the way my data comes out is that it has quantity of same item sold, but these are not separate line items.

I can't seem to get this to work for me at all - I've tried a few difference things but I think this is beyond my level of knowledge. Could anyone shed some light?

Cheers
Daniel

Excel Help Test.xlsx

[TMS]

COUNTIF with "multiple factors" would be COUNTIFS.


Regards, TMS

[daniel.d]

Hey TMS. I picked that up after I posted this but I still can't get the formula going. Any ideas?

[TMS]

Just realised you said ...

should calculate on which specified date did I sell the staple tee in the colour white and size small

Rather than COUNTIF, or COUNTIFS, I think the way to do that would be with a Pivot Table.

If you did want to use COUNTIFS, you'd need to fill in the gaps on the top header row. But COUNTIFS won't identify individual dates.


Have a look at the updated sample workbook attached.


Regards, TMS

[misrasomendra]

Hi,

Although I also feel the pivot table should be followed for such tabular data for analysis, but still I am confused about your writing on post #1, you said,
"In the attached example, AJ4 on "Test Inventory.." should calculate on which specified date did I sell the staple tee in the colour white and size small."

Do you want date of sale or total quantity for the sale with the specified condition. If you want date than what if there are multiple dates, See Staple, Large, White. What is the result you expect in this case?

[TMS]

@misrasomendra: that's why I have suggested a Pivot Table. It is possible to list all the dates for specific filters on the data. However, the tabular summary cannot work with a "simple" COUNTIFS because there are gaps in the headers. I'm sure it is possible but I suspect it might be messy to do with a formula.

Regards, TMS

[misrasomendra]

@TMS: Totally agree with you, It can be done but with a Messy looking array formula

Better to go with your solution.

[daniel.d]

Thanks for the initial help! I've attached this screenshot from my original sheet which should further help in my confusing explanation. The pivot table seems to be the most ideal way to fetch data based on the requirements - it would just need to filter by date, but on the plus side it takes into account those line items with multiple quantities.

The point of ambiguity for me is when you look at this screenshot. From what I understand, the pivot table doesn't insert into a cell and so doesn't fit my template. Would this mean that a long and messy formula is the option? But is that even doable?

Thanks again for the help, I've hit a point in Excel where I've never been so stuck ha.

Screen Shot 2014-10-20 at 3.23.53 pm.png

[TMS]

I already explained this:

If you did want to use COUNTIFS, you'd need to fill in the gaps on the top header row. But COUNTIFS won't identify individual dates.

The Pivot Table example I uploaded demonstrates how you can filter the Pivot Table.

Pictures rarely help

[TMS]

But is that even doable?

I have no doubt that someone would be able to provide a complex and messy array formula. But it is likely to be slow and difficult to maintain.

Alternatively, you could make your data (headings) more Excel-friendly.

[misrasomendra]

@daniel.d

I had changed XL,S,M,L to Small, Large, Medium and used SUMPRODUCT function to count. See the file for working, but I also still feel you must go with Pivot Table.

[daniel.d]

Thank you both of you for the help thus far - I'll be sitting down over shortly to have a proper look through this.

I have one question to you @misrasomendra. You're SUMPRODUCT all makes sense with the exception of one factor I don't understand. Could you explain *(LOOKUP("zzzz",$A1:I1)?

[misrasomendra]

@daniel.d

LOOKUP("ZZZZ",$A1:A1) is a lookup function which will return the last text in the range. Now if you see the lookup array of LOOKUP function (RED Part) is expandable range. "ZZZZ" is a big string normally the string in your Row 1 i.e. colour will always be less than this string "zzzz".

So basically say your A1 cell Grey Male Is valid for A2:E2 & A3:E3. So the combinations will be like A1,A2,A3;A1,B2,B3;A1,C2,C3 and so on. From cell F1 for the next five cells the colour will change.

So if you drag the LOOKUP function across it will return the last text so for the range A4:E4 it will return, Grey Male, from F4 the last text in the range will change to White till cell J4 and the pattern will continue.

So if you have a range like:

White _________ ________ _________ _________ Black _______ ________ _________ _________

Now if you drag this formula across it will generate something like this.
White White White White White Black Black Black Black Black

So you will have valid 3 conditions which are used in SUMPRODUCT function for calculations.

Hope this make sense, if not, than write back.

[misrasomendra]

@daniel.d

LOOKUP("ZZZZ",$A1:A1) is a lookup function which will return the last text in the range. Now if you see the lookup array of LOOKUP function (RED Part) is expandable range. "ZZZZ" is a big string normally the string in your Row 1 i.e. colour will always be less than this string "zzzz".

So basically say your A1 cell Grey Male Is valid for A2:E2 & A3:E3. So the combinations will be like A1,A2,A3;A1,B2,B3;A1,C2,C3 and so on. From cell F1 for the next five cells the colour will change.

So if you drag the LOOKUP function across it will return the last text so for the range A4:E4 it will return, Grey Male, from F4 the last text in the range will change to White till cell J4 and the pattern will continue.

So if you have a range like:

White _________ ________ _________ _________ Black _______ ________ _________ _________

Now if you drag this formula across it will generate something like this.
White White White White White Black Black Black Black Black

So you will have valid 3 conditions which are used in SUMPRODUCT function for calculations.

Hope this make sense, if not, than write back.