Excel cell formula to count keys from one table, i

I feel like the answer to this one is simple, but I just can't seem to wrap my head around it: I have a large table of historical data (example on the right) of when specific part numbers are shipped out, and whether they are one of two categories (example on the left).

Part    Type       |    Part Shipped   Date Shipped
Part#1  W          |    Part#3         23-Apr
Part#2  W          |    Part#3         25-Apr
Part#3  W          |    Part#5         17-Apr
Part#4  U          |    Part#2         18-Apr
Part#5  W          |    Part#6         25-Apr
Part#6  U          |    Part#1         18-Apr

I'm trying to get a COUNTIFS formula to tally up all [Part Shipped] entries that were between [Last Monday] and [Last Friday], but only where the [Type] is of type "W". I've gotten the date criteria down, but I can't quite puzzle out how to pass the [Part Shipped] criteria range over to the other table in order to reference and count the [Type]

The input data is taken from a live data feed(Odata, atomsvc file), so I use full columns as criteria range such as 'DATA'!X:X.

I've figured out the date criteria as such:

">=" & ((TODAY() - 7) - (WEEKDAY(TODAY()) - 2))
"<=" & ((TODAY() - 7) + (WEEKDAY(TODAY()) - 6))

and my full formula so far is as such:

=COUNTIFS('SO Progress'!X:X, ">=" & ((TODAY() -7) - (WEEKDAY(TODAY()) -2)), 'SO Progress'!X:X, "<=" & ((TODAY()-7) + (WEEKDAY(TODAY())-6)))

but this doesn't have any criteria for counting the types yet. I thought about using a simple VLOOKUP and comparing it to "W", but my main problem is figuring out how to pass the full range into the VLOOKUP formula and get results back for each line in the range.

At this point, I feel like it's probably just a matter of me not knowing how one or more of these functions or tools can be used to get what I need, since I feel like this is probably a common sort of issue. I just can't seem to find any results specific enough to help my case.

[EDIT]

Using the above data, assuming that the columns are contiguous A:D, I'm looking to first filter dates shipped (D:D) that fall within last week Monday through Friday. For example, using today (4/29), that range would be 4/22 through 4/26.

This initial pair of criteria will get me three results: Part#3, Part#3 and Part#6. However, Part#6 is of type "U", illustrated by columns A and B, so my end result tally should wind up being 2.

The A:B table contains unique part numbers and their types for reference, but shipped parts in table C:D may be duplicates and will, in most cases, be much larger than the type reference table.

回答1:

That took a while to figure out, but you can use the following array formula (use Ctrl+Shift+Enter and you should see braces in the formula bar if you did it right):

=SUMPRODUCT(--(D2:D7=TRANSPOSE(A2:A7))*TRANSPOSE(B2:B7=H2)*(E2:E7>=(Today-WEEKDAY(Today,2)-6))*(E2:E7<=(Today-WEEKDAY(Today,2)-2)))

I think it should work, I tried to play a bit with different values and it seemed to be holding up fine. In the above snapshot, Today is H1.

It will of course take a long time to calculate if your data sets are big, in which case, I would recommend doing the exercise in SQL which is more suited for this kind of operation (faster and simpler to do).

回答2:

If you're looking to count occurrences where date is greater than x, AND date is less then Y, AND z = "W", then just add another criterion to your COUNTIFS() function.

The formula below assumes dates are in column D and type is in column B - obviously adjust these to suit your worksheets.

=COUNTIFS(D:D, ">=" &TODAY()-7-WEEKDAY(TODAY()-2),D:D, "<=" & TODAY()-7+WEEKDAY(TODAY()-6),B:B,"=W")