Counting in excel [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi I have an excel spreadsheet that has totals in 4 different tabs. I have a final tab that sums up the scores of the 4 tabs.

A B C D E
93.33% 6564 TS x x
98.00% 654654 RH
100.00% 465165 RS
100.00% 4651650 RSH
100.00% 5165165 PR x
100.00% 1651651 TS x
72.73% 1561561 JS x

I am working in the newer excel and used a formula =SUMPRODUCT((Sheet1!G:G=("AD"))*(Sheet1!H:H="x")) .. to count if there is an AD in column C count how many times AD gave and X in Column D

I tried the forula in the older excel and all I got was #name ... is there another excel formula that would count how many times for example above excel colums.. how many times TS in column C gave out a X in Column D .. So I want the final result to tell me TS gave out 2 Xs in column D

This is the tab that will sum up the 4 tabs, the total counted how many times AD shows up in the table above, then How many times AD gave an X above like in column D and E above
Name Total 100% Audits Variance Audits Yes No
AD 4 1 0 0 0
JS 8 1 1 1 0
PR 8 1 1 1 0
RSH 8 1 1 0 0
RH 7 1 1 0 0
RS 16 2 2 1 1
ST 4 1 0 0 0
SM 8 2 0 1 0
TS 25 3 3 3 1

I thought I had it but when I went to open the file in an older excel, all my numbers come up as #Name .. =SUMPRODUCT((Sheet1!G:G=("AD"))*(Sheet1!H:H="x")) this didn't work anymore

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
your formula seems to be ok

I copied your data from row 2 , row 1 is heading

formula is
=SUMPRODUCT((Sheet1!C2:C8="TS")*(Sheet1!D2:D8="x"))
better use the range addresses c2:c8 instead of c:c

if "name" error comes that means there is a spelling mistake. check it up

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!