• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help to amend sumproduct formula to take into account a new column

K

Kellis

Member
Hi everyone

This is my first time posting, a relative newbee. I have slowly built up my knowledge of Excel using the internet and I am still trying to understand how to build formula.
I have a spreadsheet which I have attached a slimmed down version of, I need to understand how to build my formula.

I need a count of jobs i.e In April, 5 PP1 jobs were carried out.
I use a sumproduct formula which works great, now I have added another column for cancelled jobs what I need is my original formula to be amended to minus the cancelled jobs.

Please help, I have cross posted in ExcelForum but I do not think my explanation was very good.
Thanks in advance
 

Attachments

Hello Kellis,
Welcome to the forum.

You can subtract the canceled jobs by including only those jobs that are not canceled. (i.e. Canceled Column = "No")

Try the following formula in cell B9 on the Summary tab:
=SUMPRODUCT(('April 13 '!$H$2:$H$2000=$A9)*('April 13 '!$B$2:$B$2000=Summary!B$8)*('April 13 '!$I$2:$I$2000="No"))

You can add additional conditions using the multiplication pattern above.

Cheers,
Sajan.
 
Thanks Sajan,

This seems a good forum from the posts I've read tonight.

I still get the wrong result, the result should be three all I get is two whether I look at the "Yes" or "No" Arghh it is too late for me tonight. I will look again in the morning and hopefully see where I am going wrong.
 
Hi, Kellis!

I tried Sajan's formula in your uploaded workbook and it retrieves a 3 in B9 cell of worksheet Summary. Could you please re-check it?

A few comments about the workbook.

1) I see that you have formulas with fixed ranges up to row 2000 or 20000, which will add an unnecessary overload if there're not fully used. A workaround is to use named ranges, and even better dynamic named ranges, so as to make your model flexible without having to change any formulas, no matter the no. of rows that you might have.
Only for the formula posted you could define 3 dynamic named ranges, JobList, DateList and CancelledList. Definition is as follows for jobs, just change the column references:
JobList: =DESREF('April 13 '!$B$2;;;CONTARA('April 13 '!$B:$B)-1;1) -----> in english: =OFFSET('April 13 '!$B$2,,,COUNTA('April 13 '!$B:$B)-1,1)
Then Sajan's formula will be:
=SUMPRODUCT((DateList=$A9)*(JobList=B$8)*(CancelledList="No"))
no needing to qualify B$8 since it's in the same worksheet.

2) Column cells with non consistent data. Column A data ranges thru row 30 but has formulas from rows 145 to 274, and the formulas aren't equal. Column B idem but up to row 161, and have values and not formulas at rows 17-2, 22, 26-29. And I stopped checking.

3) In worksheet Sheet3 you have a range from I6:J17 where you have months' name and numbers from 1 to 12. I didn't find where you're using it, but if it's in column A of worksheet Summary you could do this:
A9: =NOMPROPIO(TEXTO(FECHA(AÑO(HOY());FILA()-5;1);"mmmm")) -----> in english: =PROPER(TEXT(DATE(YEAR(TODAY()),ROW()-5,1),"mmmm"))
(You can omit the PROPER function if your regional configuration settings retrieve months as April and not april, as in my case -Spanish version-)

4) Worksheet Summary, range B9:K20. Despite of Sajan's formula or my variation with names, your formulas were like this:
=IF(SUMAPRODUCT(('April 13 '!$H$2:$H$2000=A9)*('April 13 '!$B$2:$B$2000=Summary!$C$8))=0,"0",SUMAPRODUCT(('April 13 '!$H$2:$H$2000=A9)*('April 13 '!$B$2:$B$2000=Summary!$C$8)))
where if SUMPRODUCT returned a zero (numeric) you placed a zero (string), so better avoid the IF and the string assignation and let this used function return its natural value.

5) Worksheet "April 13", A2 cell. You have this formula:
=IF(ISNA(VLOOKUP(E:E,Sheet3!$A$1:$B$34,2,FALSE)),"", VLOOKUP(E:E,Sheet3!$A$1:$B$34,2,FALSE))
which'd be shortened for Excel versions 2007+ as:
=IFERROR(VLOOKUP(E:E,Sheet3!$A$1:$B$34,2,FALSE)," ")
but I assume you're using 2003 version as per the uploaded file. So just fyi.

Hope it helps and just advise if any doubt, or if you'd require a sample file with this modifications.

Regards!
 
Hi Kellis ,

Sajan's formula is correct ; your figure of 5 is wrong for two reasons :

a. In cell I3 , the data entered is not "No" , but "N o" ; please correct this.

b. In cell H24 , the data entered is not "April" , but "May" ; please correct this.

Narayan
 
Hi Everyone

I have made the changes and yes it works, thanks Sajan.

All of your time an effort is most appreciated. The uploaded file is not my actual file the correct one was at work so I made a quick summary table to give you a rough idea of what I was doing and find out how I would minus the cancelled jobs. So there are bits on this file which are redundant.

The actual file has thousands of jobs on it already and I have pieced it together with my very limited knowledge (it has been fun though) I will clear up any sensitive data and post the version I will be using, I would be grateful for any advice on it.

Thanks again this is a great site. I'm loving the Ninjas
 
Hi Everyone
Sorry it took so long for me to come back. I have uploaded a better version of the workbook this is more like the one I use.
To explain, the first sheet is copied and pasted from a nightly report. The last three column are entered each night as the report is audited.
The second sheet is a breakdown of the first
The third is just a summary table of total jobs
I have noticed the original is very slow opening and saving.
I tried a dynamic named range as suggested by SirJB7. Can anyone advise me how I can offset all the formulas on the last line of the metering sheet so that when Data is entered it will transfer automatically without the need for the formula to already be copied down the sheet (making my file unnecessarily big) Is this possible?

Also I normally have the Metering sheet protected so it cannot be amended from that sheet
 

Attachments

Hello Kellis,

I don't know your requirement of Mastering Jobs sheet. I got same result, with based on FD Input Sheet, with below formula.

Summary!B9, then copy down & across.

=SUMPRODUCT(ISNUMBER(MATCH('FD Input'!$D$2:$D$12,lookUp!$D$1:$D$36,0)/(LOOKUP('FD Input'!$D$2:$D$100,lookUp!$D$1:$E$36)=C$8))*(TEXT('FD Input'!$F$2:$F$100,"mmmm")=$B9)*('FD Input'!$I$2:$I$100="No"))

To reduce the size, select unused whole rows then use CLEAR ALL tool. I am not sure where that located in Excel 2003. If you are on Excel 2007 or later, it wil be in Home >> Cells group. key board shortcut, Alt+H+E+A

Edit: Add MATCH in SUMPRODUCT to look only EXACT job_type in lookup tab. Also for dynamic range, if you use INDEX, workbook will calculate only when opening the file or something change in source columns NOT in anywhere in the workbook.
 
You must log in or register to reply here.
Back
Top