EXcel formula help

mp3ireland2

I'm trying to set up my excel sheet so that I can summarise data for last year and this year separately.

This was my formula last year (year one of the spread sheet so year didn't matter). Here is an example of a formula I'm using, the rest are very similar so once I get one I'll have them all! 31 December is the name of sheet with all the data

=SUMPRODUCT(--('31 December'!B3:B601="ZS"),--('31 December'!N3:N601="COMPLETE")

This worked fine.

=SUMPRODUCT(--('31 December'!B3:B601="ZS"),--('31 December'!N3:N601="COMPLETE"),--('31 December'!J3:J601<"01/01/2012"))

Is what i'm trying to use now, and change the "<" sign to a ">=" sign for 2012 data summary. (This is obviously a little short sighted as I'll have to revise when i get to 2013!)

I forget a lot of my excel since college, so If anybody could help that would be appreciated! At the minute the last array doesn't seem to make any difference to the formula at all, the formula above still pulls in data for 2012, and when i change it to a ">=" sign I get O's :-(

Cheers for any help!

Will_H

Try bookboonhttp://bookboon.com/en/textbooks for free books. Some good ones on Excel...

homer911

SUMPRODUCT treats array entries that are not numeric as if they were zeros, so your formula does not make a lot of sense to me (check the example in Excel Help)

What are the double minus signs for?

Perhaps if you posted a sample with a few lines of data (only the necessary columns) we could make sense of what you are trying to do

mp3ireland2

homer911 wrote: »

SUMPRODUCT treats array entries that are not numeric as if they were zeros, so your formula does not make a lot of sense to me (check the example in Excel Help)

What are the double minus signs for?

Perhaps if you posted a sample with a few lines of data (only the necessary columns) we could make sense of what you are trying to do

What I was trying to do was on the Stats sheet to pull in the total number of items with ZS or ZE in the column for a given year, original I was thinking if statements but somewhere along my googling I seemed to think SUMPRODUCT would work better. So say for B3 I wanted [if Column A = ZS, Column E = complete and Column D < 01/01/2012] to count this entry.
B4 then would count the ones of these on time, so [if Column A = ZS, Column F = ONTIME and Column D < 01/01/2012] to count this entry.


As for the double minus, I was working on this before Christmas and can't remember where I was copying my formulae from, but strangely it only works when the "--" is in front of each array now, and I don't know why....

The file attached only has relevant columns, so the ignore all the red conditional formatting underneath, on '31 December' sheet.

Any help is appreciated as I know it's a bit of hassle for somebody to open attachment etc.

TheFairy

Not sure what version of Excel you have. Very simple using 2010 :

=COUNTIFS('31 December'!A3:A74,"ZS",'31 December'!D3: D74,">01/01/2012")

Countifs can be used to continually narrow down data, ie in the formula above we count only those that are ZS, then within that we only count those that are greater than 01/01/2012. Etc, Etc.

homer911

I would use COUNTIFS which works off multiple ranges/criteria in pairs

eg

=COUNTIFS('31 December'!A3:A200,"=ZS",'31 December'!E3:E200,"=Complete",'31 December'!C3:C200,"<01/12/2012")

If the date doesnt work for you, try creating an extra column for year of date ( eg =YEAR(C3) ), then assuming put in column G use that as the criteria eg

=COUNTIFS('31 December'!A3:A200,"=ZS",'31 December'!E3:E200,"=Complete",'31 December'!G3:G200,"<2012")

or use cell references for the year and product you want to use eg

=COUNTIFS('31 December'!A3:A200,"=" & A1,'31 December'!E3:E200,"=Complete",'31 December'!G3:G200,"<" & B1)

mp3ireland2

Thanks lads, never really used countif before and probably wouldn't have thought of using it. Works perfect, it can get frustrating when trying to figure these things out! Did a fair bit of excel in college but not using it all the much the past few years, so forgetting a lot!

Ste1605

Hi everyone - I need help in writing the code for a marco to save a text version of a file I create daily. I have attached the file I want to save and want to create a text copy of the first tab and save it down in a seperate location. I will be making a copy of this daily so would like to ensure this is saved down every day with previous days date.
Any help greatly appreciated..

Ste1605

.

homer911

File Corrupted..

Edit, no, its an XLS with an XLSX extension, containing the sample macro

Ste1605

I have re saved it and should be fine now. Cheers

homer911

Not entirely sure what you are doing with this, but this works, if not very pretty

You will need to change the parent file path

this is an XLSM file that I've changed the extension on..

Ste1605

Thanks for that will have a look now and see how I get on. Thanks again

Ste1605

Thanks for that will have a look now and see how I get on. Thanks again

Ste1605

Hi guys, Im back again.

I want to copy and paste special two files on one spreadsheet on to a new one. The spread sheet has numerous tabs on it and I only require a certain two tabs. I have run into problems trying to write the code for the second sheet.
Is this possible to record?

Many Thanks
Stephen

homer911

homer911 wrote: »

SUMPRODUCT treats array entries that are not numeric as if they were zeros, so your formula does not make a lot of sense to me (check the example in Excel Help)

What are the double minus signs for?

sumproduct with the double minus signs are similar to vlookup except they will return the "lookup" for ALL matches found, not just the first one like vlookup does
the minus sign is to check if it matches; the second minus sign is because the result is automatically returned negative so this cancels it out

it can reference letters as well as numbers

the result ends up looking like your countifs, i suspect

Ste1605

I need to add the code to paste the second sheet in after the below

Sub TxtFile()
Dim wkbk As Workbook, Filename As String, FolderName As String, InputSheet As Worksheet
Set InputSheet = ActiveSheet
FolderName = "T:\Pricing (Non-Derivatives) Loans\DailyTemp"
Filename = "prices"
Set wkbk = Workbooks.Add(xlWBATWorksheet)
wkbk.Sheets(1).Range("A:A").NumberFormat = "@"
InputSheet.Cells.Copy
wkbk.Sheets(1).Cells.PasteSpecial xlPasteValues
wkbk.Sheets(1).Cells.PasteSpecial xlPasteFormats
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=FolderName & "\" & Filename & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

homer911

It should be straightforward. I just recorded this..

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Source.xlsm").Activate
Sheets("Sheet2").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\TEMP\Book2.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Ste1605

Hi Thanks for your help, however I have tried to run this and am still expereincing the problem when it gets to begin copying sheet2. When I get to Windows("Source.xlsm").Activate it stops.

Any advice?

homer911

Sorry, the macro was in a workbook called Source.xlsm

You should use the name appropriate to you..

Taltos

OP - one trick I use all the time on macros is the F8 key - it lets me walk through a macro to see where it is going wrong / right - great for variable watching and just even seeing where you need to jump in.

On larger macros I use the pause feature (big red dot) to get to a certain point and then I F8 through the rest...