Multiple calculations in one SUMIF formula across multiple worksheets

**necht_angel** · 11-14-2011, 10:42 AM

Hi there, I hope you can help, although this seems quite complicated and I'm not sure it can be done.

Basically I have two corresponding worksheets with information contributing to the formula, and the formulae needs to calculate this information multiple times (possibly across 50 columns).

I have attached a sample which I 'hope' will explain it better than I have. (only the first two yellow sheets are relevant to the calulations)

I would be really grateful for any advice on this one. I am not particularly technically minded so 'dummy' language may be required lol

Thanks all

**NBVC** · 11-14-2011, 12:06 PM

What are some expected results..

Here is my attempt R16 cell.... I get 154 total

=SUMPRODUCT(--('(2) Rate Build Ups'!$G$17:$O$17=1),'(2) Rate Build Ups'!$H17:$P17,$G16:$O16/$S$9*$S$15)

**necht_angel** · 11-14-2011, 12:50 PM

Hi there, no i get 315.24

Basically i want if there is a '1' or any number in the columns G, I, K etc on the 'Rate Buildup' page, I want excel to add that numbr of times ie '1' '2' etc etc, the number in the corresponding column on the 'material page', i.e. slopes i.e. 1 x 100 + 1 x 110 + 2 x 120 + 1 x 20 = 420, then divided by R9 (1.64) = 286.58 + S15 (10%) = 315.24.

**NBVC** · 11-14-2011, 01:34 PM

So then you are not looking for just 1's in the Rate Build Ups...

Try:

=SUMPRODUCT('(2) Rate Build Ups'!$G17:$O17, 'Material Schedule'!$G16:$O16)/$R$9*$S$15