Basic Excel Data Snapshot Help

[SSS1203]

Hello there,

I need some help on Excel. I have a large range of data, with columns with certain filters, including dates, numerical amounts and action being taken.

The spreadsheet itself does it's job, whenever we need a specific part of data, such as data from a specific month, and the amounts, it's easy to filter. However, I need some help in trying to improve this spreadsheet to make it even more efficient.

I'd like to basically have monthly snapshots of the data I need.

For example, instead of filtering the month of MARCH in the spreadsheet to see a NUMBER VALUE of something, I'd like there to be a formula that automatically does the maths in a separate tab for that specific request. Is this possible?

I don't know whether it's a lot of "sum ifs", if so I'd like to know the easiest way to do it, as my knowledge of excel is pretty basic.

A B C
1 03/18 10,000 Actioned
2 03/18 5,000 Actioned
3 02/18 6,000 To be Actioned
4 03/18 5,000 Actioned
5 04/18 5,000 Actioned

So for this example, I'd like an automatic formula for the total value of B if data was ACTIONED but only in the month of MARCH.

I would appreciate any help! Cheers

 

[SSS1203]

|A ........|B ......|C
1 03/18 10,000 -Actioned
2 03/18 5,000 -Actioned
3 02/18 6,000 -To be Actioned
4 03/18 5,000 -Actioned
5 04/18 5,000 -Actioned

 

[Joe4]

Welcome to the Board!

Let's say that your data is on Sheet 2 in range A1:C5 and that the dates in column B are entered as actual dates and not text (they can be formatted any way you like, that doesn't matter).
Then this formula will return what you want:

=SUMPRODUCT(--(MONTH(Sheet2!A1:A5)=3),--(Sheet2!C1:C5="Actioned"),Sheet2!B1:B5)

 

[SSS1203]

Hi thanks for your quick response,

Having a bit of trouble here.

So my Dates are currently in column A

Numerical value that needs calculated is Column B

Action/Not in column B


A------------------B--------------C
12/03/2018 ----- 100,000 ---- Actioned
12/03/2018 ----- 100,000 ---- Actioned
12/04/2018 ----- 200,000 ---- Actioned
12/04/2018 ----- 100,000 ---- Actioned
12/05/2018 ----- 100,000 ---- Actioned


I tried that formula and it seemed to not work. Any ideas?

 

[Joe4]

How is it not working?
Are you receiving errors or unexpected results?
Please post your exact formula (after the edits you made), and the result you are getting.

Sorry, I misspoke in my previous example. Like your example, I have dates in column A, amounts in column B, and action in column C.
I wrote the formula with that structure in mind.

If it is not working for you, here are some things to verify:

- Are the dates in column A entered as "Dates" or "Text"?
One way to check is to go to your first date (12/03/2018) and change the Format of that cell to General. Does it change it to a number like 43171?
If it does, then it is a date. Excel stores dates as numbers, specifically the number of days since 1/0/1900.
It is does not change how it looks, then you have a text entry, and date functions like MONTH will not work on them.

- Are you values in column B entered as numbers?
Usually a dead giveaway is in they are left or right-justified. By default, text is left-justified and numbers are right-justified

- Are there any extra characters or spaces at the beginning or end of your entries in column C?
Easy way to check is to click on an entry and hit F2. The cursor will jump to the end of the entry. Is there a space between the last letter and the cursor?
Then click Home, and it will jump to the beginning of the entry. Likewise, is there a space between the first letter and the cursor?

- Did you remember to update the formula I gave you to reflect the Sheet name that the data appears on?
I used "Sheet2", but your sheet name is probably different.