How do I sum based on the BGColor and Condition

[Harishkk]

Hello All,

Am trying to make a report for my Clients based on the currently running projects, whats needs to be renewed and what I have lost for the Quarters.

I was trying to use SumProduct(), but using that am not able to check the condition of the BG Color.

=SUMPRODUCT(($C$3:$E$23)*($A$3:$A$23=$B$27)*<BGCOLOR=GREEN>)

I have attached the sample.

Appreciate all you help in advance.

Thanks & Regards,
Harish

 

[Hui]

Harish

I think this is what you want:


I have unmerged your headings in both tables and given each column its own values

I used a formula in B28 not dissimilar to yours except that it references a UDF
=SUMPRODUCT(MatchColor($A28,$C$3:$H$23)*($A$3:$A$23=B$27)*($C$1:$H$1=B$26)*($C$3:$H$23))
The UDF Matchcolor() returns an Array which is the same size as the data area and hence can be used in the Sumproduct

Refer to the attached file:

 

[Harishkk]

Harish

I think this is what you want:
View attachment 18195

I have unmerged your headings in both tables and given each column its own values

I used a formula in B28 not dissimilar to yours except that it references a UDF
=SUMPRODUCT(MatchColor($A28,$C$3:$H$23)*($A$3:$A$23=B$27)*($C$1:$H$1=B$26)*($C$3:$H$23))
The UDF Matchcolor() returns an Array which is the same size as the data area and hence can be used in the Sumproduct

Refer to the attached file:

Hello Hui,

Thank you so much, you made is very simple for me.

Appreciate your help.

Thanks & Regards,
Harish

 

[Harishkk]

Hello All,

Am working on Costing for my project, my FY starts from Nov to Oct.

I need to print Quarterly values based on the month I start from

For example if I start from Sep then my calculation should be for 12 months.

Q4 - Sep to Oct
Q1 - Nov to Jan
Q2 - Feb to Apr
Q3 - May to Jul
Q4 - Aug

I was trying to use Sum Product and Choose formula but wasn't able to do it.

Request to help me to how this can be done

I have attached the sample.

Appreciate all you help in advance.

Thanks & Regards,
Harish

 

[NARAYANK991]

Hi Harish ,

Since this question has no connection with your earlier question , you are expected to start a new thread and post this question in that thread. Please follow this rule in future.

Since you say your financial year goes from November to October , your quarter start dates are :

November 1 , February 1 , May 1 and August 1

Can you say whether the date you will enter in R3 will be only from the above 4 dates , or can it be any date of the year ?

Narayan

 

[Khalid NGO]

Hello All,

Am working on Costing for my project, my FY starts from Nov to Oct.

I need to print Quarterly values based on the month I start from

For example if I start from Sep then my calculation should be for 12 months.

Q4 - Sep to Oct
Q1 - Nov to Jan
Q2 - Feb to Apr
Q3 - May to Jul
Q4 - Aug

I was trying to use Sum Product and Choose formula but wasn't able to do it.

Request to help me to how this can be done

I have attached the sample.

Appreciate all you help in advance.

Thanks & Regards,
Harish

Hi Harish,
Try this for QTR-1:
=SUM(IF(MONTH(C2:N2)={1;11;12},C5:N5))

QTR-2:
=SUM(IF(MONTH(C2:N2)={2;3;4},C5:N5))

QTR-3:
=SUM(IF(MONTH(C2:N2)={5;6;7},C5:N5))

QTR-4:
=SUM(IF(MONTH(C2:N2)={8;9;10},C5:N5))

Note: these are array formulas, which must be entered with Ctrl+Shift+Enter, not just enter.

Regards,

P.S. I think you should start new thread for new question.
Edit: Didn't noticed Narayan Sir already replied.

 

[Harishkk]

Hi Harish ,

Since this question has no connection with your earlier question , you are expected to start a new thread and post this question in that thread. Please follow this rule in future.

Since you say your financial year goes from November to October , your quarter start dates are :

November 1 , February 1 , May 1 and August 1

Can you say whether the date you will enter in R3 will be only from the above 4 dates , or can it be any date of the year ?

Narayan

Hello Narayan,

Apologies for using the earlier thread. I tried using new conversation but it was asking for participants and I didnt see an option to raise it.

I might have been looking at the wrong place.

To answer your question, the month can be any month of the year, for a duration of 12months.

For ex: if I start from 7/1/2015 then it should start from Jul 2015 to Jun 2016

FY2015 - Q3: $xxx
FY2015 - Q4: $xxx
FY2016 - Q1: $xxx
FY2016 - Q2: $xxx
FY2016 - Q3: $xxx

Please let me know if you need further details.

Thanks & Regards,
Harish

 

[Hui]

@harish

To start a new thread goto http://forum.chandoo.org/
Select the appropriate Forum eg: Ask an Excel Question
Then use the Post New Thread button

 

[Nebu]

Hi:

May be something like this.
OOPs @Hui did not see your post...
Thanks

 

[Harishkk]

@harish

To start a new thread goto http://forum.chandoo.org/
Select the appropriate Forum eg: Ask an Excel Question
Then use the Post New Thread button
View attachment 20755

Thanks Hui for letting me know the procedure for starting a new thread.

Will take care in future.

Thanks & Regards,
Harish