Free Microsoft Excel 2013 Quick Reference

Sum based on date cell not being empty Results

Hi there,

Looking for a bit of help with a formula, it's been years since I've used excel properly.

Here's what I'm trying to do:

I have a list of items and their prices, some of these items have been bought already and have the date of purchase stored in a date formatted cell.

I have a running total of these various items but I only want to sum the ones which have been purchased already ie only those with a purchase date.

I have the logic of how it should be done in my head, I just can't translate that in to a working formula.

Any help would be appreciated.

Cheers.

Anyone have any suggestions for the following issues that I've come across:

1. I'm trying to in column "AE" to have a "net deposit balance" based on several cells, which some of the cells in question may or may not have data in them. This is where my problem lies. If any of the cells identified below in my formula is blank (i.e. "") I get a #value error displayed. Anyone have any ideas how I might overcome a blank/empty cell, but still have this formula work & check any of the other cells for possible data to add without giving me an error sign?

This is what I tried to put in my AE4 cell:
=(J4+N4+S4+AD4)-P4

2. I've tried formatting a column for a "complaint #" column, which would be entered in the following manner: 07-2322
I had the column formatted to "general", but whenever this particular entry is made it gives me a date "Jul-22" display and I can't seem to change it to read the complaint # above. It appears to do this to certain number arrangements. I'd sure like to know a way to fix this too if anyone has experienced this similarily.
Thanks in advance!

Thanks for any help! I have struggled with this for ages. Could i use an AGGREGATE function here??? Situation outlined below, good luck!:

Ultimately I am looking to subtract values in a row of cells from a total and count the number of cells it takes for the total to reach <0.

I am working on a spreadsheet which looks at calculate/forecast the date of strawberry ripening. The number of days from a flower to ripening is based on the sum of "24hr average temperatures".

The number of days it takes a strawberry flower to turn into a ripe fruit is based on a Sum of 24hr Avg Temperatures which is has to reach (this means the average temperature over each 24 hours is added together to reach the total REQUIRED).

I will be inputting the daily 24hr average temperatures as well as the number of flowers appearing each day.

I will also have the "REQUIRED sum of 24 hr average temperature" for a flower to reach ripening (for now lets call it 750*C).

For each individual count date (date when flowers are counted) I will subtract the "accumulated 24hr avg temperatures" over the period; which is a sum of all "24hr avg temps" from the "TOTAL REQUIRED sum of temps" to leave me with the "REMAINING TOTAL REQUIRED temp."(for example each day i add an OBSERVED 24hr avg temp, it will subtract the sum of all OBSERVED temps from the count date until the last OBSERVED temp, from the REQUIRED total).

To determine the remaining number of days until ripening i will use a forecast of "24hr avg temps." which will be in individual cells across the COLUMNS (each COLUMN is a different Date).

Bearing in mind that the forecasted 24hr average temperature will be different for each date.

I want to be able to calculate the number of days it will take for the REMAINING TOTAL REQUIRED to fall to <0 (less than 0).

This will have to involve using the REMAINING TOTAL REQUIRED and subtracting the forecasted temps in sequence until the REAMAINING TOTAL REQUIRED is less than 0, and the count number of dates/individual 24hr average temps it took to reach that point.

Either it will add the number of days on to the number of days already passed (a simple count function of dates with cells that arent empty and contain observed temps could look this up) or add them on to "today's" date (the date at which the data is being viewed).

I have attached the spreadsheeet which i intend to use. THere are different groups which refer to areas of strawberry fields which are not particularly important.

What i would like is to either retrieve a harvest DATE/number of days LEFT until ripening/total days unti ripening (including ACTUAL days passed + number of days left based on subtracting daily forecast temp from REQUIRED REMAINING temp)

Thanks for all your time!

All the best,

Tony

Hi all
One of my colleagues at work has asked my help to see if there could be macro based solution that could help her with one certain task she has to perform which is very frustrating for her. I have played around with some different formulas and have not had any luck to figure it out myself.

Objective is to find out the total sum of quantities for given part that potentially has been ordered within the last 30 days. Its bit hard to explain the objective in one sentence… Let me just crack on with what the macro should actually do.

There are two sheets and 6 columns involved. Let’s call these two sheets as “Procured Parts” and “Procured Parts delivered”. On both of these sheets there are the following columns that we are concerned with: C, E, F, H, J. Answers are populated on sheet “Procured Parts” in column L. Column L on sheet “Procured Parts delivered” is not to be checked in any way nor its values are to be changed.

Easiest way to explain anything is to have example data of before and after macro. First column is for identification of examples (i.e. ex1, ex2, etc..). Data before macro as below.

Before macro

sheet “Procured Parts”

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex1 --------- TLD ------------------ 30/09/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 1
Ex2 --------- TLD Plus ----------- 01/10/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 2
Ex3 --------- AB ------------------- 30/10/2011 --------- Customer one --------- P111111111 --------- 1 ------------ ----
Ex4 --------- TLD ------------------ 30/10/2011 --------- Spares ------------------ P222222222 --------- 1 ------------ ----
Ex5 --------- TLD Plus ----------- 30/10/2011 --------- Customer one --------- P333333333 --------- 2 ------------ ----
Ex6 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P444444444 --------- 1 ------------ ----
Ex7 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P555555555 --------- 2 ------------ ----

sheet “Procured Parts delivered”

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex8 --------- TLD ------------------ 01/06/2011 --------- Customer two --------- P444444444 --------- 1 ------------ 1
Ex9 --------- TLD ------------------ 30/09/2011 --------- Customer two --------- P555555555 --------- 3 ------------ 3
Ex10 ------- AB ------------------- 01/10/2011 --------- Customer two --------- P555555555 --------- 4 ------------ 4
Ex11 ------- TLD ------------------ 01/10/2011 --------- Spares ------------------ P555555555 --------- 4 ------------ 4
Ex12 ------- TLD ------------------ 01/10/2011 --------- Customer two --------- P555555555 --------- 4 ------------ 4

The first half of the macro would just check if certain conditions are met on sheet “Procured Parts”. If all conditions are met it will start to sum up certain quantities found in column J.

It would check the following:
Col L - check col L if there is a empty cell, If yes then proceed (ex 3,4,5,6,7), if cell is not empty ,then do not proceed to next step (Ex1,2).
Col C – Check if cell equals “TLD” or “TLD PLUS”, if yes then proceed (ex 4,5,6,7), if not (ex 3) then populate value “n/a” to Col L
Col F – Check if cell equals “Customer one” or “Customer two”, if yes then proceed (ex 5,6,7, if not (ex 4) then populate value “n/a” to Col L

Now if all 3 checks cleared then it needs to check some more columns in order to do a calculation as below.
Macro needs to check column H, E and J
Column H – check for a part number. We need to compare apples with apples. Lets say the first part where we would need to perform calculations is part number P333333333 (ex 5). Macro would look quantities for the same part number (ex 1,2)
Column E – date check. Macro would look quantities that are within 30 days of this date stated in column E (inclusive)
Column J – macro would sum the quantities of column J if all criterias are met.

Macro has to sum up the quantities from both sheet that meet the criteria. Lets do some examples with some parts.

Ex1 and Ex2 - nothing is touched with these two lines as Column L already has value within it.

Ex 3 - P111111111 - column C does not have “TLD” or “TLD PLUS” so "n/a" is populated to col L

Ex4 - P222222222 - column F does not have “Customer one” or “Customer two” so "n/a" is populated to col L

Ex 5 - P333333333 - checks in col C and F are ok so we proceed with the macro. We are now checking part P333333333 on both sheets. Ex1 and Ex 2 on sheet “Procured Parts” meet the criteria. Checks with Ex 1,2 in col C and F are OK . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 5 (which is 30/10/2011). Ex 1 does not meet this criteria as its 31 days away, Ex 2 meets the criteria as its within 30 days. Values in column J of Ex 2 and Ex 5 are summed up which would be quantity of 3.

Ex 6 - P444444444 - checks in col C and F are ok so we proceed with the macro. We are now checking part P444444444 on both sheets. Ex 8 on sheet “Procured Parts delivered” meets the criteria
Check with Ex 8 in col C and F is OK . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 6 (which is 30/10/2011). Ex 8 does not meet this criteria as its 131 days away. Values in column J of Ex 6 are summed up which would be quantity of 1.

Ex 7 - P555555555 - checks in col C and F are ok so we proceed with the macro. We are now checking part P555555555 on both sheets. Ex 9,10,11,12 on sheet “Procured Parts delivered” meet the criteria. Checks with Ex 9,12 in col C and F are OK (Ex 10,11 are not ok) . We are only summing up the lines that are within the 30 days from the date stated in col E of Ex 7 (which is 30/10/2011). Ex 9 does not meet this criteria as its 31 days away, Ex 12 meets the criteria as its within 30 days. Values in column J of Ex 7 and Ex 12 are summed up which would be quantity of 6.

So after macro the sheet “Procured Parts” would look like this (values in Bold Green are the new values):
After macro

Ex. ---------- Col C ---------------- Col E ----------------- Col F ---------------------- Col H -------------------- Col J -------- Col L
Ex1 --------- TLD ------------------ 30/09/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 1
Ex2 --------- TLD Plus ----------- 01/10/2011 --------- Customer one --------- P333333333 --------- 1 ------------ 2
Ex3 --------- AB ------------------- 30/10/2011 --------- Customer one --------- P111111111 --------- 1 ------------ n/a
Ex4 --------- TLD ------------------ 30/10/2011 --------- Spares ------------------ P222222222 --------- 1 ------------ n/a
Ex5 --------- TLD Plus ----------- 30/10/2011 --------- Customer one --------- P333333333 --------- 2 ------------ 3
Ex6 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P444444444 --------- 1 ------------ 1
Ex7 --------- TLD ------------------ 30/10/2011 --------- Customer two --------- P555555555 --------- 2 ------------ 6

I have attached a spreadsheet. I used my colleagues tracker, I took out all the sensitive information, made up these scenarios as above. After running the macro sheet "Procured Parts" should match with sheet "After".

Any help would be very appreciated as my colleague she is a lovely older lady who would find this very useful.

Cheers


No luck finding an answer? You could always try Google.