Nesting an IF statement in a SUMPRODUCT

[doublej_jj]

Windows XP / Windows 2007

I use SUMPRODUCT to pull data to a summary sheet based on the criteria of a date range and company name. The summary sheet can be an individual company or a consolidation of all companies, aka "All". I currently use the following formula where B13 is the company name.

=IF($B13="All",SUMPRODUCT((Date_H>=I$10)*(Date_H<=I$9)*(Company_H<>"")*(Cont_Disc_H="Cont")*(Rev_Types_H=$A13)*(Rev_Data_H)),SUMPRODUCT((Date_H>=I$10)*(Date_H<=I$9)*(Company_H=$B13)*(Cont_Disc_H="Cont")*(Rev_Types_H=$A13)*(Rev_Data_H)))

This formula does work. However, I want a simplified, more elegant and more easily read formula. Since the true and false results above are identical except for the company name, I have tried to nest the company condition in a single SUMPRODUCT using IF at the end to determine consolidated or individual.

=SUMPRODUCT((Date_H>=G$10)*(Date_H<=G$9)*(Cont_Disc_H="Cont")*(Rev_Types_H=$A13)*(Rev_Data_H)*(IF($B13="All",1,(Company_H=$B13))))

This works for the consolidate / ALL. However, it returns a value of zero for all other company names. I have ensured that the Company_H=$B13 is correct by deleting everything else in the IF statement and it is correct, also verifiable by the formula that works above.

So the bottom line is, "Can anyone tell me if there is a reason why this does not work?" Any assistance is appreciated!

Jeff

 

[rdw72777]

Well I'm never one for changing things that don't work and don't cause performance issues, but here's waht I see.

1. Between your 2 formulas, the second formula is evaluating Date_H against G9 and G10, while the first formula is using I9 and I10. not sure if this has any impact, but please make sure you're using the cell ceferences).
2. In your second formula, at the end where you have (Company_H=$B13), highlight the Company_H text and hit F9 and what value do you get?. Then hit escape, highlight $B13, hit F9, and what value do you get?

I know you said you verified this, but it seems the formula doesn't agree....

Get back to us.

 

[barry houdini]

Hello Jeff, welcome to MrExcel, try like this

=SUMPRODUCT((Date_H>=G$10)*(Date_H<=G$9)*(Cont_Disc_H="Cont")*(Rev_Types_H=$A13)*(Rev_Data_H)*(($B13="All")+(Company_H=$B13)))

 

[doublej_jj]

Barry:

Genius!!! The simplicity is too good. I am notably a novice with the sumproduct formula. I love that I don't even need to nest an IF in there. Thank you greatly for the solution.

Ryan:

Great appreciation for your reply. The date concern was just the same formula in different columns, each column representing a different time period. The other refences were solid after I checked them. I do thank you for looking at it.

Jeff