noviceuser
New member
- Joined
- Jun 12, 2019
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- excel 2010
I am wanting to return information based on certain criteria being met.
I have used SUMPRODUCT and COUNTIF for each separate stage, but not linked them together and I have hit a brick wall.
What I am trying to achieve is,
1, Filter out the information by month (this is an annual sheet)
2, Lift how many vans a person has sold split into new and used (P-O), as in COUNTIF "BE" column K if E is not blank, sumproduct column U into summary tab - E5 - Sales Rev, repeat for column V into summary F5 - profit. But I need to discount if column O (workshop costs) is blank.
I have been doing this so far by manually filtering the new and p-o,copying onto a separate workbook, then sourcing the information. This is no longer practicable.
as this document is constantly updated I wanted to have this as an automatic summary and would like to avoid using a VBA / macro if at all possible. (these have not worked well in our workplace before).
sorry if this makes no sense.
View attachment Sample Van sales.xlsx
I have used SUMPRODUCT and COUNTIF for each separate stage, but not linked them together and I have hit a brick wall.
What I am trying to achieve is,
1, Filter out the information by month (this is an annual sheet)
2, Lift how many vans a person has sold split into new and used (P-O), as in COUNTIF "BE" column K if E is not blank, sumproduct column U into summary tab - E5 - Sales Rev, repeat for column V into summary F5 - profit. But I need to discount if column O (workshop costs) is blank.
I have been doing this so far by manually filtering the new and p-o,copying onto a separate workbook, then sourcing the information. This is no longer practicable.
as this document is constantly updated I wanted to have this as an automatic summary and would like to avoid using a VBA / macro if at all possible. (these have not worked well in our workplace before).
sorry if this makes no sense.
View attachment Sample Van sales.xlsx