getting an aggregate sum based on another column 1

[flashbbeb]

Hello,

I'm working on a summary report with calculations of a specific count based on data on another sheet (called "Q108") in the same file. My summary report (called "Summary") must count the number of courses from the other sheet, listed as either ILT or e-learning.

An example of the data on Q108:

C ................. R
ILT 4
ILT 27
ILT 5
e-learning 90
ILT 53
e-learning 24
e-learning 7
ILT 18

I want the calculation to give me 107.


An explanation of the calculation formula I want on Summary:

In column C, where the value = ILT, get the value in the same row for column R and get an aggregate sum.

Can anyone please help?

Thanks,
EB

 

[Lilliabeth]

You said "count" a few times, but it looks to me like you want to sum, not count.

Have a look at SUMIF.

--Lilliabeth

 

[combo]

For regular table with headers you can use pivot table, course type as page field.
If both summary data and report is on the same sheet, SUBTOTAL with aggregation function 9 (sum) and autofilter will sum visible records.

combo

 

[flashbbeb]

Lilliabeth - you're right. I'm really looking for a sum here. I've been doing a lot with SUMPRODUCT recently, it didn't occur to me to use SUMIF. Thanks!

Combo - I considered a pivottable, but would prefer to use a formula in this particular case. Thanks!