Finding values in multiple headers using an Array formula

[Hazlitt]

Hello,

I am trying to work with data that has multiple headers and multiple similar values using an array formula. My goal is to use three criteria to obtain values and arrive at a sum.

The multiple headers contain dates of product arrival and the columns contain sku's and PO's (both containing multiple same values).

In this case, I'm looking to find anything arriving on the Nov 10 date on a CA PO.

My formula looks like this:
{=SUM(IF($C$4:$E$4<=$H$2+5,IF($B$5:$B$22=$G5,IF($A$5:$A$22<"D*",$C$5:$E$22))))}

I'm unable to obtain values from the latter headers. I have attached the sheet for reference.

Any help would be greatly appreciated!

[63falcondude]

Hello and welcome to the forum.

Your data is set up very poorly. I would recommend restructuring it if you ever plan on doing any analysis on it (like you are here).

This means having a single row at the top for your headers, then the data going down below that.

Also, you would be better off sharing the desired results rather than the results of a non-working formula.



That being said,

[Glenn Kennedy]

I'm not sure what you mean by: "I'm unable to obtain values from the latter headers", but... try this:

=SUMPRODUCT(($C$4:$E$21<=$H$2+5)*($B$5:$B$22=G5)*($A$5:$A$22<"D*")*$C$5:$E$22)

failing that, then do as FD suggested and tell us you EXPECTED answers. A non-working formula doesn't help us at all....

[AMoreno]

Whoops I take that back. Listen to them haha.

However, it is not adding the other values because the format of them are text. Reformat each range to number and adjust to the format the way you want and then re-enter the values that are irregular to the others. (The ones that are green flagged are the ones that need changed)

[Hazlitt]

Thanks Falcon Dude and Glenn Kennedy. I have amended my original post to clarify my intended result.

My goal is to sum the amount of deliveries on X date by sku and by PO. My formula and Glenn's formula worked similarly in that it is able to find the data from the top header by date but not the second set.

I agree that the data is poorly organized. It is coming from one of my suppliers so I was wondering if I could do analysis on it in its present form. Garbage in garbage out I suppose. It may not be feasible.

Thanks for the welcome.

[63falcondude]

You still haven't shared your desired results so I cannot compare this formula to anything (and am therefore guessing that it gives you what you're looking for).

Try this:

H5 =SUMPRODUCT((LEFT(A$5:A$10,2)="CA")*(B$5:B$10=G5)*(C$4:E$4=H$2)*(C$5:E$10))+SUMPRODUCT((LEFT(A$14:A$22,2)="CA")*(B$14:B$22=G5)*(C$13:E$13=H$2)*(C$14:E$22))

Drag the formula down.

[Hazlitt]

Thanks Falcon Dude.

This formula works perfectly for this sample data set. My intended goal was to be able to obtain a sum of products being delivered based on date, PO and SKU. The data set that I would be applying this to is 600+rows with multiple headers which is why I'm looking for a broader formula if possible. As you mentioned before, the data is set up terribly so I'll probably have to get my supplier to restructure the way they send the information.

Thanks again.

[Hazlitt]

I'm getting the data reorganized for proper analysis (attached).

Using the reference sheet my desired result is to sum all values that are being delivered within a date range and to copy it into a forecast spreadsheet based on sku, PO# and date.


Using this array formula, I am able to obtain the value I want but only for a set date. I'm looking to expand this to capture a wider array of dates from the reference sheet.

=SUM(IF(REFERENCE!$E$1:$I$1=$E$18,IF(REFERENCE!$B$3:$B$79=$A3,IF(REFERENCE!$A$3:$A$79>"D*",REFERENCE!$E$3:$I$79))))

Explained

=SUM(IF(date range=date,IF(reference=sku,IF(reference finds all PO's greater than >"D*"(for US PO's,sum totals))))


I have tried >=date+2 to no avail.

Any help would be appreciated.

[Glenn Kennedy]

Use SUMPRODUCT (non-array):

=SUMPRODUCT((REFERENCE!$E$1:$KP$1>=$E$18)*(REFERENCE!$E$1:$KP$1<=$E$19)*(REFERENCE!$B$3:$B$29=$A3)*(REFERENCE!$A$3:$A$29>"D*")*(REFERENCE!$E$3:$KP$29))

see sheet

[Hazlitt]

Thanks Glenn! This works perfectly. + reputation.

Is there a way to get the sumproduct formula to ignore text within the calculation range? I want to put some container shipping info in between the PO's. It works with the sum(if) but not with sumproduct.

[Glenn Kennedy]

Then you need an array....

=SUMPRODUCT(IFERROR((REFERENCE!$E$1:$KP$1>=$E$18)*(REFERENCE!$E$1:$KP$1<=$E$19)*(REFERENCE!$B$3:$B$29=$A3)*(REFERENCE!$A$3:$A$29>"D*")*(REFERENCE!$E$3:$KP$29),""))

Apols for the delay... I've been on the road for days!!

[Hazlitt]

No need for apols, you were extremely helpful. I also found a SUMPRODUCT quirk in my experiments. When I put in the formula with a , instead of a * for the last field, it was able to skip over any text in-between without needing the array! Very strange.

=SUMPRODUCT((REFERENCE!$I$1:$AG$1>=R$20)*(REFERENCE!$I$1:$AG$1<=R$21)*(REFERENCE!$B$3:$B$93=$A4)*(LEFT(REFERENCE!$A$3:$A$93,2)="CA"),(REFERENCE!$I$3:$AG$93))

I have marked this thread as solved. Thanks again.