Yes, this worked. Thank you so much all.

I'm trying to return the hours now. I used the formula below for 1/1/05 and it worked great but when I copy down it throws an error. The only references that are changing are the Short Date and the Max value which is what I want, I don't understand why it would work for the first row but not any others.

MAXHOUR
=INDEX(B:G,MATCH(IF(B:B=K4,L4),G:G,0),5)


Edit: K4 is short date (which lists the calendar day sequentially row by row
L4 is the max daily price

Edit: The issue is that =IF(B:B=K5,L5) returns FALSE. I have no idea why this is happening.

I figured it out with arrays:
=INDEX(B:G,MATCH(1,(B:B=K4)*(G:G=L4),0),5)

Still don't know why my first approach didn't work after Row 1.

If I have a column of number data, is there a way to run a formula that scans that column and is able to spit out all possible combinations of numbers that equal to a certain total?

If I have a column of
3
8
7
4
3
6

Could I run a formula looking for all possible combinations that total 11 and have it give me

Lines 1 and 2, lines 3 and 4, lines 2 and 5, etc.

My first thought is something along the lines of this formula, assuming your data startings in column A.

=IF($A$1+A2=11,"row"&ROW($A$1)&"&"&ROW(A2),"")

Drag down to end of data in column A starting in cell B1.

Now in cell C1, you need the same formula except the locked cell should be A2.

Somebody else may have a better method.

=SUMIFS(MTT!N:N,MTT!A:A, A789,MTT!K:K, ">=1", MTT!N:N, ">0", MTT!H:H, "*added*")

I'm counting overlays in MTTs, but I don't want to include any that have the tag "added" (column H). The above formula only includes ones that do include that tag. How do I make it NOT be those?

I can't do =SUMIFS([etc]) - SUMIFS([etc], MTT!H:H, "*added*") because I want to exclude several terms but some entries will include more than one of those terms.

Use a MIN(1, SUM(conditions)) for the terms?

A common question here is: What's the best way to learn VBA? The answer is: Have a project and search internet for ways to accomplish it. With that in mind, here's a "project" you can work on:

http://adventofcode.com/

a) it's not new - I heard about it last December, started it, and forgot about it. Just came back to it.

b) VBA is certainly not going to be the optimal language for solving every problem. But I'm through 5 days and so far I've found fairly efficient solutions using VBA in Excel.

c) it will give you experience with variables, loops, counters, booleans, string parsing, arrays, IF-THEN-ELSE, etc

d) it won't give experience with excel objects like sheets and workbooks, opening/saving files, querying external data sources, etc (at least through day 5)

Anyway, it's pretty fun if you like coding and critical thinking.

I've tried to make a spreadsheet for MTT results. I used the table function so that it would auto update ranges for a couple of graphs. The cumulative columns are =--SUBTOTAL(9,P$12:P92) and similarly R12->, -- because without them the filter breaks for some odd reason.



Now tho when I add new entries to the list the original last row's subtotal updates to the end of the new table, so now the original last row with the "Hyper" is =--SUBTOTAL(9,P$12:P95) after adding 3 rows.



It's obv easy to dragcopy the last 2 columns to fix it after adding new rows but does anyone know a simple automated way?

I'm having a difficult time combining COUNTIFS and SUM. I know COUNTIFS by default is using an AND condition, so it's tough to include a range within the formula.

I have 2 "must" conditions, but want the third condition to be a must condition across a range of columns.

Example:



How do I find out how many white clients we Closed via Phone when Closed via Phone can be in either D, E or F columns?

Also I know I don't need the SUM in there. I was trying to combine the SUM(COUNTIFS with the array, but it's only for multiple criteria in a single range, not multiple.

Not sure how to do it in 1 formula but out to the right you could have 1 equation where you see if "white" and another formula sees is "closed by phone" is in any of the 3 columns. Then you can check to see if both of those equations are true and total that column.

yeah, I find myself using the "create a few more columns with binary results" method as a workaround a lot of the time when I'm sure a single formula exists.

So in this case I'd just have the following in cell G2
=if(D2="closed by phone",1,"")
.. then copy it across to columns H and I, have column J as to sum G-I, and copy the whole thing down. If the value in B is "White" and the value in J is >0, you have a white client closed by phone.

I'm keen to learn about the inevitable single formula solution though!

You can definitely do it by array formulae but I suck at them. Alternatively, you can add countifs - countif column d is closed by phone and column b is white; + countif column e is closed by phone and column b is white; + countif column f is closed by phone and column b is white.

In column G put this equation.

=IF(COUNTIF(D2:F2,"Closed by Phone")>0,IF(COUNTIF(C2,"White"),"YES",""),"")

Then you can just count how many "YES" cells there are in column G to get the number you need.

Right after I posted this, I just made a concatenate of all the Status columns and did a search within that to mark Closed via Phone. It worked, but I want the easier way. Going to play with NWC's suggestion a bit later. Thanks for the help.

Hi. I have taken over the management role of a private marina in a gated residential community. Each week I go around all the boats (170) to make sure that they are in the right berths and that their insurance stickers, registration, electrical compliance is all up to date. The home owners assoc secretary gives me a full copy of an excel spreadsheet with all this info on it, about 50 columns wide with boat details, owner details, the house that the berth is attached to details, details if the berth is rented out and the renters details etc etc . I Just select the columns that I need and take a print out on my physical walk. Problem is the home owners secretary is very slack in keeping this database updated so I waste allot of time on issues that don't exist.

I do not know what database system they are using but the info they give me is on excel. The association is looking at sacking the secretary and handing over the full database responsibility to me. On my physical walks it would be good if when there is an issue, say the safety tag on a power lead is out of date, that I could note it and auto send the owner an email and all the details of this event were recorded somewhere. One of the residents said I should get a new database / CRM package but I was wondering if excel had the power to do all this kind of stuff. If so could you recommend somewhere I could do online training on excel to get up to speed quickly? Otherwise could you recommend a package that might suit my needs? Cheers!

It's easy on Excel. You don't need a database to keep track of 8500 pieces of information. CRM package makes some kind of sense, but it doesn't seem likely you'll be sending out many mails so I expect it'd be more trouble than it's worth. Sounds to me that the problem was just someone not updating the sheet.

Use SUMIFS() function on the two columns...

i'm trying to make a function for a column that returns an error message if there is more than one column of cells with showing a value for a given row (over a specified range).

so for example say the range is B : D and the error-checking column is F. if B2 is showing a value, any value showing in C2 and/or D2 will return an error in F2. if only B2, or only C2, or only D2 is showing a value, F2 should be blank.

i tried using COUNTA but it looks like it counts cells that have functions but aren't showing an output. i just want the error to show if there are multiple visible values showing in the row of cells.

COUNTIFS should work

what do i use for the criteria to detect any kind of value?

=IF(COUNTIFS(B2 : D2,"")>1,"Error","")

i assume i need to replace the first set of quotes with something since the above is detecting blank cells, but idk what. (or maybe i'm just doing it completely wrong because i'm a noob)

ah looks like this works (at least for my case since all the values are numbers > 0)

=IF(COUNTIFS(B2 : D2,">=0")>1,"Error","")

Here's a screenshot of a sheet I'm working on:



Column C is formatted on a colour scale with bright red for the lowest value, white for zero, bright green for the max value. I want the cells in column B to appear in the same colours as their adjoining cells in C (as I intend to hide column C), but I can't figure out how to achieve this.

I'm aware of how to format a single cell based on another cell's value, but what's confusing me here is the fact that each cell in C is already formatted according to the values of the rest of the column! Any help?

You'd need to use vba

I think you could do it, more or less, without using VBA. For instance, you could put in conditions on the cell on the right being the max or min of the range. You could also use the choose function, but it would be a hassle (should work though I think).