Very complicated SUMPRODUCT formula

[zookeepertx]

Hello all!

I have been struggling with this ridiculous formula for longer than I even want to admit, so now I'm here begging for help.
I'll try to explain this as clearly as I can:

What I have to work with:

• Sheet 1 is called "Totals"
• Sheet 2 is called "COMPLETE"
• Totals has a table on it where data from COMPLETE needs to be organized. The table is B4:N10.
• On Totals the year is in C2 and the months - from AUG-JUL - are in C3-N3.
• On Totals, O2 contains a formula that gives the last row number on COMPLETE that contains data. Also, there will be a varying number of rows at any given time as data is added or deleted.
• On COMPLETE, the columns we're concerned with are A, F, L and Q.
• Column A contains a date which could be varying months, date or year and is formatted as mm/dd/yyyy
• The other columns are just varying data

What I'm trying to do:

• On Totals, from C4-N4 counts how many rows on COMPLETE>column A are within the month and year from combining Totals C3-N3.
  • I have this working with this formula: SUM(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0" & (MONTH(C$3&1))&"/"&$C2),1,0))
• ALL other formulas will relate to only the rows on COMPLETE where A is for the particular month. (C5 refers to the month represented by C3/C2 = 08/2017).
• So, C5 only involves rows on COMPLETE where A = any day in 08/2017.
  • It needs to count how many of those rows have "ALLOCATION" in column F on COMPLETE.
• So, D5 only involves rows on COMPLETE where A = any day in 09/2017.
  • It needs to count how many of those rows have "ALLOCATION" in column F on COMPLETE...
  • I have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="ALLOCATION"))
• Now, the first problem -
  • Here's what C6 on Totals needs to count on COMPLETE:
  • If Q is NOT the word YES and is NOT blank
  • If L is either the word NO or is colored red, but has NOT already been counted because of the data in Q
  • I do NOT have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF(NOT(TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="YES"),1,0)*((--(IF(NOT(TEXT(INDIRECT("COMPLETE!$L$2:$L$"&$O$2),"@")="NO"),1,0)))))))
  • SORRY! THIS NEXT PART SHOULD BE AT THE LEFT MARGIN BECAUSE IT'S SEPARATE FROM C6 BUT I CAN'T SEEM TO EDIT THAT.
  • C7 on Totals needs to count the rows on COMPLETE where F is "GLC Not Loaded".
    • I have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="GLC NOT LOADED"))
• Second problem -
  • Here's what C8 on Totals needs to count on COMPLETE:
    • Any rows where F says "DOMESTIC/LANDED" or "WRONG SUPPLIER SITE".
  • Here's what C9 on Totals needs to count on COMPLETE:
    • Any rows that have not already been counted in F.

That's all there is to it! (I do realize this is insane, but then, I work for crazy people, so there's that...)

I included the formulas that I currently have working so that, if you see that there's an easier way you can tell me and so that you can see what's there now in case that helps you to visualize this mess, LOL!

Can all of this even be done? The co-worker originally thought about a macro, but I got to thinking that having dynamic formulas on the Totals sheet would have the workbook be up to the minute at all times. If it would be easier with a macro, that's fine, too.

Thank you for even reading this novel!

Jenny

 

[CyrusTheVirus]

a picture is worth a thousand words... can you please copy/paste a sample of your tables/data showing before results and after results (what you want it to look like)?

 

[zookeepertx]

Okay, I'll give it a shot. Sometimes the board likes to fight with me, LOL!

I'm including the row numbers and column letters (the ones that border the spreadsheet, just to make it easier to reference them.

Here is a sample of the data on the sheet "COMPLETE": (I've taken out most of the data, leaving just the pertinent information, to make it easier to read). I can't seem to make cell backgrounds red on the table, so the ones where the font is red represent that cell being red.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

APPROVE DATE

PO #

ORDER TYPE

ON EDI?

RMS - BOTH EDI & IMPORT FLAG?

2

08/01/17

GLC NOT LOADED

YES

EDI

3

08/01/17

ALLOCATION

NO

YES

4

09/03/17

ALLOCATION

YES

YES

5

08/01/18

GLC NOT LOADED

YES

YES

6

08/03/17

DOMESTIC/LANDED

10005832

7010

7

09/03/17

IMPORT ORDER

YES

YES

8

09/03/17

ALLOCATION

YES

YES

9

08/01/17

WRONG SUPPLIER

NO

YES

10

09/03/17

DOMESTIC/LANDED

YES

YES

11

09/03/17

GLC NOT LOADED

YES

YES

12

10/03/17

ALLOCATION

YES

YES

13

09/03/17

ALLOCATION

NO

NO

14

15

08/01/17

GLC NOT LOADED

YES

YES

16

17

18

19

08/01/17

ALLOCATION

NO

YES

20

09/03/18

DOMESTIC/LANDED

YES

YES

21

08/01/17

ALLOCATION

YES

<tbody>
</tbody>

Here is how the "Totals" sheet needs to look; I'm only putting in the first few columns of results, to save space. I've included some explanatory information for how the results are calculated.
Column A shows the formulas that are in the results cells for each row. Column B has the criteria for that row, followed by the text in the cell on the spreadsheet. Column C, row 6 shows how the count in that cell was counted, given the data on COMPLETE.

	A	B	C	D	E
1	ROWS 5 AND 20 ON COMPLETE DON'T GET COUNTED AT ALL BECAUSE THEY ARE IN 2018
2			2017
3			AUG	SEP	OCT
4	SUM(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0" & (MONTH(C$3&1))&"/"&$C2),1,0))	(Total rows on COMPLETE for the month indicated) Total Approved Import Purchase Orders	7	6	1
5	SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="ALLOCATION"))	(ALLOCATION in F for the month indicated) Purchase Orders not Allocated	3	3	1
6	SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*((--(IF(NOT(TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="YES"),1,0)*(IF((TEXT(INDIRECT("COMPLETE!$L$2:$L$"&$O$2),"@")="NO"),1,0))))))	(Not YES in Q ; Not BLANK in Q; NO or red color in L if not YES or blank in Q) THIS FORMULA IS ONLY WORKING FOR THE DATA IN Q. I CAN'T FIGURE OUT HOW TO INCLUDE THE DATA IN L PO not Flagged Correctly (BULK,Import,EDI) <tbody> </tbody>	2 cells in Q that aren't YES or blank. 3 cells in L that are NO and 1 cell that's red (even though it says YES) 6	1 cell in Q that's not YES or blank. 1 cell in L that's NO, but it doesn't get counted because that row already got counted because of Q 1	Q only has 1 cell and it says YES, so not counted. But the cell in L is colored red, so it does get counted 1
7	SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="GLC NOT LOADED"))	(GLC NOT LOADED in F) GLC Not Loaded	1	1	0
8	SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*((--(IF((TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="DOMESTIC"),1,0)+(IF((TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="WRONG SUPPLIER"),1,0))))))	(DOMESTIC/LANDED or WRONG SUPPLIER in F) Incorrect Supplier Site/ Landed Vendor	2	1	0
9		Miscellaneous
10		New Vendor

<tbody>
</tbody>

I am absolutely the WORST at trying to explain things, so I hope this makes sense. (I forget just how bad I AM at explaining until I try to do it, then I wonder how anybody understands ANYTHING I'm trying to say. )

Thank you for looking at this!

Jenny

 

[zookeepertx]

Sometimes the board likes to fight with me, LOL!

(It just occurred to me that might have sounded wrong. I didn't mean that the PEOPLE on the board like to fight with me; I meant the board itself! Just like the ACTUAL internet - it hates me! LOL!
The PEOPLE on the board are wonderful!)

 

[Aladin Akyurek]

The best way is to create a small sample (input) along with the desire output (with no formulas).

 

[zookeepertx]

The best way is to create a small sample (input) along with the desire output (with no formulas).

I don't see any way to attach a workbook or sheet to a message. Am I just missing it somehow when I go to post? Since this is at work, I don't think I'd be allowed to upload a workbook to a "sharing" website. And I'm finding the tables that can be created within a message to not be very friendly (at least, not to me. But that's probably more of a statement on ME than anything else)

I'm starting to think I may have to give up on this being a dynamic table and just do this with a macro. I'd hoped to be able to have it showing the correct information all the time, without having to run a macro. I'm afraid I may have to surrender, in which case, I'm sure I'll need help writing the macro, LOL!

I have to get out of here in a few minutes, but I'll be back to revisit this on Monday.

Thanks

Jenny

 

[Eric W]

After working through your list of requirements, and your tables, I think we can probably come up with some formulas for you, and probably improve a bit on the ones you already have. HOWEVER, the monkey wrench in the works is your requirement in C6 to count cells colored red. A formula cannot detect coloring of a cell. How are those cells colored? If by Conditional Formatting, we can probably duplicate the rule that CF uses in the formulas. If they are manually colored, we can't use native Excel formulas.

Option 1 is to come up with some kind of helper column, where the user puts an X into a cell instead of, or in addition to, coloring the L cell red. Then we can look for the X.

Option 2 is to write a UDF (User-Defined Function), which can detect coloring. We'd need to know the shade of red to look for, or maybe just anything that isn't the default background color.

Option 3 is to write a VBA macro that performs all the calculations at a time. Possibly the easiest way would be to set up an event handler that triggers whenever you select the "Totals" sheet. Depending on how the data is entered in the "COMPLETE" sheet, the totals would always be up to date when you look at them.

Let us know if any of those options sound workable.